package com.huabo.contract.oracle.mapper;

import com.alibaba.druid.util.StringUtils;
import com.hbfk.util.DateUtil;
import com.hbfk.util.PageInfo;
import com.huabo.contract.oracle.entity.TblCyhwProjectbudget;
import com.huabo.contract.oracle.entity.TblCyhwUnit;
import com.huabo.contract.oracle.entity.TblStaff;
import com.huabo.contract.util.DateUtils;

import java.math.BigDecimal;

public class TblCyhwUnitMapperSqlConfig {

    public String selectUnitListByPageInfo(PageInfo<TblCyhwUnit> pageInfo, String orgid, String staffid, Integer paCount) throws Exception {
        TblCyhwUnit unit = pageInfo.getCondition();
        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,ROWNUM RN FROM (SELECT tcu.CONTRACTID,tcu.FLOWID,tcu.UNITNAME,tcu.CONTRACTNAME,tcu.CONTRACTNO,tcu.CONTRACTDEPT,tcu.CONTRACTLINK,tcu.CONTRACTMONEY,tcu.CONTRACTSTATUS,tcu.STARTDATE,tcu.ENDDATE,tcu.CONTRACTITEM,tcu.CONTRACTTYPE,tcu.CONTRACTDATETYPE,tcu.CONTRACTXZ,tcu.RECORDTYPE,tcu.DCTYPE,tcu.CONTRACTBD,tcu.MOMOCONCAT,tcu.DESCRIBE,tcu.CONTRACTZD "
        		+ " from TBL_CYHW_UNIT tcu "
        		+ "LEFT JOIN TBL_CYHW_PROJECTBUDGET tcp ON tcu.CONTRACTXDFXINFO = tcp.BUDGETID  "
        		+ "where tcu.ORGID="+orgid);

        if(paCount != 0) {
            sqlSb.append(" AND tcu.CREATEUSER = '"+staffid+"'");
        }
        if(unit.getBudgetid() != null && !"".equals(unit.getBudgetid())) {
            sqlSb.append(" AND tcu.CONTRACTXDFXINFO = "+unit.getBudgetid()+"");
        }
        if(unit.getContractno()!=null && !"".equals(unit.getContractno())){
            sqlSb.append(" AND tcu.CONTRACTNO LIKE '%"+unit.getContractno()+"%'");
        }
        if(unit.getContractname() != null && !"".equals(unit.getContractname())) {
            sqlSb.append(" AND tcu.CONTRACTNAME LIKE '%"+unit.getContractname()+"%'");
        }
        if(unit.getContractbd() != null && !"".equals(unit.getContractbd())) {
            sqlSb.append(" AND tcu.CONTRACTBD LIKE '%"+unit.getContractbd()+"%'");
        }
        if(unit.getContracttype() != null && !"".equals(unit.getContracttype())) {
            sqlSb.append(" AND tcu.CONTRACTTYPE LIKE '%"+unit.getContracttype()+"%'");
        }
        if(unit.getLinkdept() != null && !"".equals(unit.getLinkdept())) {
            sqlSb.append(" AND tcu.LINKDEPT = "+unit.getLinkdept()+"");
        }
        if(unit.getBudgetname() != null && !"".equals(unit.getBudgetname())) {
            sqlSb.append(" AND tcp.BUDGETNAME LIKE '%"+unit.getBudgetname()+"%'");
        }

        if(unit.getRecordtype()!=null && !"".equals(unit.getRecordtype())){
            sqlSb.append(" AND tcu.RECORDTYPE = '"+unit.getRecordtype()+"' ");
        }
        if(unit.getContractstatus() != null && !"".equals(unit.getContractstatus())){
            sqlSb.append(" AND tcu.CONTRACTSTATUS = "+unit.getContractstatus()+"");
        }
        if(unit.getContractdept() != null) {
        	sqlSb.append(" AND tcu.CONTRACTDEPT = "+unit.getContractdept()+"");
        }
        
        
        sqlSb.append(" ORDER BY tcu.CONTRACTID DESC ) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;
    }

    public String selectUnitCountByPageInfo(PageInfo<TblCyhwUnit> pageInfo, String orgid, String staffid, Integer paCount) {
        TblCyhwUnit unit = pageInfo.getCondition();
        StringBuffer sqlSb = new StringBuffer("SELECT COUNT(0) from TBL_CYHW_UNIT tcu LEFT JOIN TBL_CYHW_PROJECTBUDGET tcp ON tcu.CONTRACTXDFXINFO = tcp.BUDGETID  where tcu.ORGID="+orgid);
        if(paCount <= 0) {
            sqlSb.append(" AND tcu.CREATEUSER = '"+staffid+"'");
        }
        if(unit.getContractno()!=null && !"".equals(unit.getContractno())){
            sqlSb.append(" AND tcu.CONTRACTNO LIKE '%"+unit.getContractno()+"%'");
        }
        if(unit.getContractname() != null && !"".equals(unit.getContractname())) {
            sqlSb.append(" AND tcu.CONTRACTNAME LIKE '%"+unit.getContractname()+"%'");
        }
        if(unit.getContractbd() != null && !"".equals(unit.getContractbd())) {
            sqlSb.append(" AND tcu.CONTRACTBD LIKE '%"+unit.getContractbd()+"%'");
        }
        if(unit.getContracttype() != null && !"".equals(unit.getContracttype())) {
            sqlSb.append(" AND tcu.CONTRACTTYPE LIKE '%"+unit.getContracttype()+"%'");
        }
        if(unit.getLinkdept() != null && !"".equals(unit.getLinkdept())) {
            sqlSb.append(" AND tcu.LINKDEPT = "+unit.getLinkdept()+"");
        }
        if(unit.getRecordConcatname() != null && !"".equals(unit.getRecordConcatname())) {
            sqlSb.append(" AND tcp.BUDGETNAME LIKE '%"+unit.getRecordConcatname()+"%'");
        }

        if(unit.getRecordtype()!=null && !"".equals(unit.getRecordtype())){
            sqlSb.append(" AND tcu.RECORDTYPE = '"+unit.getRecordtype()+"' ");
        }
        if(unit.getContractstatus() != null && !"".equals(unit.getContractstatus())){
            sqlSb.append(" AND tcu.CONTRACTSTATUS = "+unit.getContractstatus()+"");
        }
        if(unit.getContractdept() != null) {
        	sqlSb.append(" AND tcu.CONTRACTDEPT = "+unit.getContractdept()+"");
        }
        String sql = sqlSb.toString();
        return sql;
    }

    public String selectLedgerListPageInfo(PageInfo<TblCyhwUnit> pageInfo) throws Exception {
        TblCyhwUnit unit = pageInfo.getCondition();
        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,ROWNUM RN FROM (SELECT TCU.CONTRACTID,TCU.FLOWID,TCU.CONTRACTNAME,TCU.CONTRACTNO,TCU.CONTRACTMONEY,TCU.CONTRACTSTATUS,TCU.CREATETIME,TCU.RECORDTYPE,TCU.CONTRACTITEM,TCPJ.PROJECTNAME AS TOPICNAME,TCU.CONTRACTTYPE,TCU.DCTYPE,TCP.BUDGETNAME FROM TBL_CYHW_UNIT TCU LEFT JOIN TBL_CONTRACT_PROJECT TCPJ ON TCU.TOPICID = TCPJ.PROJECTID  LEFT JOIN TBL_CYHW_PROJECTBUDGET TCP ON TCU.CONTRACTXDFXINFO = TCP.BUDGETID WHERE TCU.RECORDTYPE IN ('HTGL002','HTGL005') AND TCU.ORGID = "+unit.getOrgid()+" AND TCU.CONTRACTID NOT IN (SELECT RECORDPARENT FROM TBL_CYHW_UNIT WHERE RECORDTYPE = 'HTGL005' AND ORGID = TCU.ORGID AND RECORDPARENT IS NOT NULL) ");

        if(unit.getCreateuser() != null) {
            sqlSb.append(" AND TCU.CREATEUSER = "+unit.getCreateuser()+"");
        }
        if(unit.getContractno() != null && !"".equals(unit.getContractno())) {
            sqlSb.append(" AND TCU.CONTRACTNO LIKE '%"+unit.getContractno()+"%'");
        }
        if(unit.getContractname() != null && !"".equals(unit.getContractname())) {
            sqlSb.append(" AND TCU.CONTRACTNAME LIKE '%"+unit.getContractname()+"%'");
        }
        if(unit.getContractitem() != null && !"".equals(unit.getContractitem())) {
            sqlSb.append(" AND TCPJ.PROJECTNAME LIKE '%"+unit.getContractitem()+"%'");
        }
        if(unit.getContracttype() != null && !"".equals(unit.getContracttype())) {
            sqlSb.append(" AND TCU.CONTRACTTYPE LIKE '%"+unit.getContracttype()+"%'");
        }
        if(unit.getBudgetname() != null && !"".equals(unit.getBudgetname())) {
            sqlSb.append(" AND TCP.BUDGETNAME LIKE '%"+unit.getBudgetname()+"%'");
        }
        if(unit.getDctype() != null && !"".equals(unit.getDctype())) {
            sqlSb.append(" AND TCU.DCTYPE = '"+unit.getDctype()+" '");
        }
        if(unit.getContractstatus() != null) {
            if(unit.getContractstatus() == 0) {
                sqlSb.append(" AND (TCU.CONTRACTSTATUS = " + unit.getContractstatus()+" OR TCU.CONTRACTSTATUS IS NULL)");
            }else {
                sqlSb.append(" AND TCU.CONTRACTSTATUS = "+unit.getContractstatus()+"");
            }
        }
        if(unit.getMinMoney() != null) {
            sqlSb.append(" AND TCU.CONTRACTMONEY >= '" + unit.getMinMoney()+"'");
        }
        if(unit.getMaxMoney() != null) {
            sqlSb.append(" AND TCU.CONTRACTMONEY <= '" + unit.getMaxMoney()+"'");
        }

        if(unit.getStartdate() != null) {
            sqlSb.append(" AND TO_DATE(TO_CHAR(TCU.CREATETIME,'YYYY-MM-DD'), 'YYYY-MM-DD') >= TO_DATE('"+ DateUtils.parseDate(unit.getStartdate(), "yyyy-MM-dd")+"', 'YYYY-MM-DD')");
        }
        if(unit.getEnddate() != null) {
            sqlSb.append("AND TO_DATE(TO_CHAR(TCU.CREATETIME,'YYYY-MM-DD'), 'YYYY-MM-DD') <= TO_DATE('"+DateUtils.parseDate(unit.getEnddate(), "yyyy-MM-dd")+"', 'YYYY-MM-DD')");

        }
        sqlSb.append(" ORDER BY TCU.CONTRACTID DESC ) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;
    }

    public String selectLedgerCountPageIfo(PageInfo<TblCyhwUnit> pageInfo) {
        TblCyhwUnit unit = pageInfo.getCondition();
        StringBuffer sqlSb = new StringBuffer("SELECT COUNT(0) FROM TBL_CYHW_UNIT TCU LEFT JOIN TBL_CYHW_PROJECTBUDGET TCP ON TCU.CONTRACTXDFXINFO = TCP.BUDGETID LEFT JOIN TBL_CONTRACT_PROJECT TCPJ ON TCU.TOPICID = TCPJ.PROJECTID WHERE TCU.RECORDTYPE IN ('HTGL002','HTGL005') AND TCU.ORGID = "+unit.getOrgid()+" AND TCU.CONTRACTID NOT IN (SELECT RECORDPARENT FROM TBL_CYHW_UNIT WHERE RECORDTYPE = 'HTGL005' AND ORGID = TCU.ORGID AND RECORDPARENT IS NOT NULL)");
        if(unit.getCreateuser() != null) {
            sqlSb.append(" AND TCU.CREATEUSER = "+unit.getCreateuser()+"");
        }
        if(unit.getContractno() != null && !"".equals(unit.getContractno())) {
            sqlSb.append(" AND TCU.CONTRACTNO LIKE '%"+unit.getContractno()+"%'");
        }
        if(unit.getContractname() != null && !"".equals(unit.getContractname())) {
            sqlSb.append(" AND TCU.CONTRACTNAME LIKE '%"+unit.getContractname()+"%'");
        }
        if(unit.getContractitem() != null && !"".equals(unit.getContractitem())) {
            sqlSb.append(" AND TCPJ.PROJECTNAME LIKE '%"+unit.getContractitem()+"%'");
        }
        if(unit.getContracttype() != null && !"".equals(unit.getContracttype())) {
            sqlSb.append(" AND TCU.CONTRACTTYPE LIKE '%"+unit.getContracttype()+"%'");
        }
        if(unit.getRecordConcatname() != null && !"".equals(unit.getRecordConcatname())) {
            sqlSb.append(" AND TCP.BUDGETNAME LIKE '%"+unit.getRecordConcatname()+"%'");
        }
        if(unit.getDctype() != null && !"".equals(unit.getDctype())) {
            sqlSb.append(" AND TCU.DCTYPE = "+unit.getCreateuser()+"");
        }
        if(unit.getContractstatus() != null) {
            if(unit.getContractstatus() == 0) {
                sqlSb.append(" AND (TCU.CONTRACTSTATUS = " + unit.getContractstatus()+" OR TCU.CONTRACTSTATUS IS NULL)");
            }else {
                sqlSb.append(" AND TCU.CONTRACTSTATUS = "+unit.getContractstatus()+"");
            }
        }
        if(unit.getMinMoney() != null) {
            sqlSb.append(" AND TCU.CONTRACTMONEY >= '" + unit.getMinMoney()+"'");
        }
        if(unit.getMaxMoney() != null) {
            sqlSb.append(" AND TCU.CONTRACTMONEY <= '" + unit.getMaxMoney()+"'");
        }

        if(unit.getStartdate() != null) {
            sqlSb.append(" AND TO_DATE(TO_CHAR(TCU.CREATETIME,'YYYY-MM-DD'), 'YYYY-MM-DD') >= TO_DATE('"+ DateUtils.parseDate(unit.getStartdate(), "yyyy-MM-dd")+"', 'YYYY-MM-DD')");
        }
        if(unit.getEnddate() != null) {
            sqlSb.append("AND TO_DATE(TO_CHAR(TCU.CREATETIME,'YYYY-MM-DD'), 'YYYY-MM-DD') <= TO_DATE('"+DateUtils.parseDate(unit.getEnddate(), "yyyy-MM-dd")+"', 'YYYY-MM-DD')");

        }
        String sql = sqlSb.toString();
        return sql;
    }


    public String findLedgerListForExport(TblCyhwUnit unit) throws Exception {
        String sql = " SELECT TCU.CONTRACTNO,TCU.CONTRACTNAME,TCPJ.PROJECTNAME AS TOPICNAME,TCU.CONTRACTMONEY,TCU.CONTRACTITEM,TCU.CONTRACTTYPE,TCU.CREATETIME,TCU.STARTDATE,TCU.ENDDATE,TCP.BUDGETNAME AS recordConcatname,ZXB.ORGNAME AS choicecontractDeptId,ZXU.ORGNAME AS choicejbunitid,TCU.CONTRACTSTATUS,TS.REALNAME AS topic,	(SELECT COUNT(0) FROM TBL_CYHW_UNIT_ATT WHERE  CONTRACTID = TCU.CONTRACTID) AS nodeCount,YYS.CREATETIME AS yongyintime,TCU.DCTYPE FROM TBL_CYHW_UNIT TCU LEFT JOIN TBL_CYHW_PROJECTBUDGET TCP ON TCU.CONTRACTXDFXINFO = TCP.BUDGETID LEFT JOIN TBL_ORGANIZATION ZXB ON TCU.CONTRACTDEPT = ZXB.ORGID LEFT JOIN TBL_ORGANIZATION ZXU ON TCU.ZXUNIT = ZXU.ORGID LEFT JOIN TBL_STAFF TS ON TCU.CONTRACTSTAFF = TS.STAFFID LEFT JOIN TBL_CONTRACT_PROJECT TCPJ ON TCU.TOPICID = TCPJ.PROJECTID LEFT JOIN TBL_CYHW_PROJECTBUDGET YYS ON TCU.CONTRACTID = YYS.RECORDPARENT AND YYS.RECORDTYPE = 'HTGL003'";
        sql += " WHERE TCU.RECORDTYPE IN ('HTGL002','HTGL005') AND TCU.ORGID = "+unit.getOrgid()+" AND TCU.CONTRACTID NOT IN (SELECT RECORDPARENT FROM TBL_CYHW_UNIT WHERE RECORDTYPE = 'HTGL005' AND ORGID = "+unit.getOrgid()+" AND RECORDPARENT IS NOT NULL)";
        if(unit.getCreateuser() != null) {
            sql += " AND TCU.CREATEUSER = " + unit.getCreateuser();
        }
        if(unit.getContractno() != null && !"".equals(unit.getContractno())) {
            sql += " AND TCU.CONTRACTNO LIKE '%" + unit.getContractno()+"%'";
        }
        if(unit.getContractname() != null && !"".equals(unit.getContractname())) {
            sql += " AND TCU.CONTRACTNAME LIKE '%" + unit.getContractname()+"%'";
        }
        if(unit.getContractitem() != null && !"".equals(unit.getContractitem())) {
            sql += " AND TCPJ.PROJECTNAME LIKE '%" + unit.getContractitem()+"%'";
        }
        if(unit.getContracttype() != null && !"".equals(unit.getContracttype())) {
            sql += " AND TCU.CONTRACTTYPE = '" + unit.getContracttype()+"'";
        }
        if(unit.getRecordConcatname() != null && !"".equals(unit.getRecordConcatname())) {
            sql += " AND TCP.BUDGETNAME LIKE '%" + unit.getRecordConcatname()+"%'";
        }
        if(unit.getDctype() != null && !"".equals(unit.getDctype())) {
            sql += " AND TCU.DCTYPE = '" + unit.getDctype()+"'";
        }
        if(unit.getContractstatus() != null) {
            if(unit.getContractstatus() == 0) {
                sql += " AND (TCU.CONTRACTSTATUS = " + unit.getContractstatus()+" OR TCU.CONTRACTSTATUS IS NULL)";
            }else {
                sql += " AND TCU.CONTRACTSTATUS = " + unit.getContractstatus();
            }
        }
        if(unit.getMinMoney() != null) {
            sql += " AND TCU.CONTRACTMONEY >= '" + unit.getMinMoney()+"'";
        }
        if(unit.getMaxMoney() != null) {
            sql += " AND TCU.CONTRACTMONEY <= '" + unit.getMaxMoney()+"'";
        }

        if(unit.getStartdate() != null) {
            sql += " AND TO_DATE(TO_CHAR(TCU.CREATETIME,'YYYY-MM-DD'), 'YYYY-MM-DD') >= TO_DATE('"+DateUtils.parseDate(unit.getStartdate(), "yyyy-MM-dd")+"', 'YYYY-MM-DD')";

        }
        if(unit.getEnddate() != null) {
            sql += " AND TO_DATE(TO_CHAR(TCU.CREATETIME,'YYYY-MM-DD'), 'YYYY-MM-DD') <= TO_DATE('"+DateUtils.parseDate(unit.getEnddate(), "yyyy-MM-dd")+"', 'YYYY-MM-DD')";

        }
        sql += "ORDER BY TCU.CONTRACTID ASC";
        return sql.toString();
    }

    public String selectCountByPageInfo(PageInfo<TblCyhwProjectbudget> pageInfo, TblStaff staff, Integer taskCount) {
        StringBuffer sqlSb = new StringBuffer("SELECT COUNT(*) from TBL_CYHW_PROJECTBUDGET where ORGID = "+staff.getCurrentOrg().getOrgid());
        if(taskCount > 0) {
            sqlSb.append(" and (BUDGETID in (select FROMID from TBL_MY_TASK where USRID = "+staff.getStaffid()+" ) or CREATEUSER = "+staff.getStaffid()+" )");
        }
        TblCyhwProjectbudget budget = pageInfo.getCondition();
        if(budget.getBudgetname() != null && !"".equals(budget.getBudgetname())) {
            sqlSb.append(" AND budgetName LIKE '%"+budget.getBudgetname()+"%'");
        }
        if(budget.getFlowid() != null && !"".equals(budget.getFlowid())) {
            sqlSb.append(" AND FLOWID = "+budget.getFlowid()+"");
        }
        if(budget.getRecordtype() != null && !"".equals(budget.getRecordtype())) {
            sqlSb.append(" AND RECORDTYPE = '"+pageInfo.getCondition().getRecordtype()+"'");
        }
        if(budget.getInspectionstatus() != null && !"".equals(budget.getInspectionstatus())) {
            sqlSb.append("AND INSPECTIONSTATUS = '"+budget.getInspectionstatus()+"'");
        }
        if(budget.getCounterpartno()!=null && !"".equals(budget.getCounterpartno())) {
            sqlSb.append(" AND counterpartNo LIKE '%"+budget.getCounterpartno().trim()+"%'");
        }

        if(budget.getProjectstagegoal() != null && !"".equals(budget.getProjectgoal())) {
            sqlSb.append(" AND PROJECTSTAGEGOAL LIKE '%"+budget.getProjectgoal().trim()+"%'");
        }
        if(budget.getCounterpartaddress() != null && !"".equals(budget.getCounterpartaddress())) {
            sqlSb.append(" AND COUNTERPARTADDRESS LIKE '%"+budget.getCounterpartaddress().trim()+"%'");
        }
        if(budget.getIsblack() != null) {
            if( budget.getBlacktype()==3) {
                sqlSb.append(" AND ((ISBLACK = 2 OR ISBLACK IS NULL) OR (ISBLACK = 1 AND BLACKTYPE = 1 AND EFFECTDATE <= SYSDATE))");
            }else if(budget.getIsblack() == 2) {
                sqlSb.append(" AND ISBLACK = 2");
            }else if(budget.getIsblack() == 1) {
                sqlSb.append(" AND (ISBLACK = 1 AND BLACKTYPE = 1 AND EFFECTDATE <= SYSDATE)");
            }
        }
        String sql = sqlSb.toString();
        return sql;
    }

    public String findCollectionChoiceContractPid(PageInfo<TblCyhwUnit> pageInfo, BigDecimal pid, String contractno, String contractname) {
        StringBuffer sbSql = new StringBuffer("SELECT * FROM ( SELECT BUDGET.*,ROWNUM RNUM FROM (SELECT TCU.CONTRACTID,TCU.CONTRACTNAME,TCU.CONTRACTNO,TCU.CONTRACTMONEY,TCU.CONTRACTTYPE,TCU.STARTDATE,TCU.ENDDATE,TCP.BUDGETID,TCP.BUDGETNAME,TCB.BANKID,TCB.BANKACCOUNT" +
                " FROM TBL_CYHW_UNIT TCU " +
                " LEFT JOIN TBL_CYHW_PROJECTBUDGET TCP ON TCU.CONTRACTXDFXINFO = TCP.BUDGETID " +
                " LEFT JOIN TBL_COUNTERPART_BANKINFO TCB ON TCU.COUNTERPARTBANK = TCB.BANKID "+
                "WHERE TCU.RECORDTYPE IN ('HTGL002','HTGL005') AND TCU.ORGID = " + pid + " AND TCU.CONTRACTSTATUS = 7 AND TCU.CONTRACTID IN (SELECT PROJECTID FROM TBL_CONTRACT_PLANNODE WHERE PLANNODESTATUS = 2) AND TCU.DCTYPE = '收款' ");
        if (contractno != null && !"".equals(contractno)) {
            sbSql.append(" AND TCU.CONTRACTNO LIKE '%" + contractno + "%'");
        }
        if (contractname != null && !"".contentEquals(contractname)) {
            sbSql.append(" AND TCU.CONTRACTNAME LIKE '%" + contractname + "%'");
        }

        sbSql.append(" ORDER BY TCU.CONTRACTID DESC) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RNUM > "+pageInfo.getCurrentRecord());
        String sql = sbSql.toString();
        return sql;
    }

    public String findCollectionChoiceContractPidCount(BigDecimal pid, String contractno, String contractname) {
        StringBuffer sbSql = new StringBuffer("SELECT count(*) FROM TBL_CYHW_UNIT TCU WHERE TCU.RECORDTYPE IN ('HTGL002','HTGL005') AND TCU.ORGID = " + pid + " AND TCU.CONTRACTSTATUS = 7 AND TCU.CONTRACTID IN (SELECT PROJECTID FROM TBL_CONTRACT_PLANNODE WHERE PLANNODESTATUS = 2) AND TCU.DCTYPE = '收款' ");
        if (contractno != null && !"".equals(contractno)) {
            sbSql.append(" AND TCU.CONTRACTNO LIKE '%" + contractno + "%'");
        }
        if (contractname != null && !"".contentEquals(contractname)) {
            sbSql.append(" AND TCU.CONTRACTNAME LIKE '%" + contractname + "%'");
        }

        sbSql.append(" ORDER BY TCU.CONTRACTID DESC");
        String sql = sbSql.toString();
        return sql;
    }

    public String insertCyhwUnit(TblCyhwUnit tcu) {
        StringBuffer column = new StringBuffer("INSERT INTO TBL_CYHW_UNIT (CONTRACTID");
        StringBuffer value = new StringBuffer(" VALUES (HIBERNATE_SEQUENCE.nextval");

        if(tcu.getFlowid() != null) {
            column.append(",FLOWID");
            value.append(",'"+tcu.getFlowid()+"'");
        }
        if(tcu.getUnitname() != null) {
            column.append(",UNITNAME");
            value.append(",'"+tcu.getUnitname()+"'");
        }
        if(tcu.getContractname() != null) {
            column.append(",CONTRACTNAME");
            value.append(",'"+tcu.getContractname()+"'");
        }
        if(tcu.getContractno() != null) {
            column.append(",CONTRACTNO");
            value.append(",'"+tcu.getContractno()+"'");
        }
        if(tcu.getContractdept() != null) {
            column.append(",CONTRACTDEPT");
            value.append(",'"+tcu.getContractdept()+"'");
        }
        if(tcu.getContractlink() != null) {
            column.append(",CONTRACTLINK");
            value.append(",'"+tcu.getContractlink()+"'");
        }
        if(tcu.getContractmoney() != null) {
            column.append(",CONTRACTMONEY");
            value.append(",'"+tcu.getContractmoney()+"'");
        }
        if(tcu.getContractstatus() != null) {
            column.append(",CONTRACTSTATUS");
            value.append(",'"+tcu.getContractstatus()+"'");
        }
        if(tcu.getLinkdept() != null) {
            column.append(",LINKDEPT");
            value.append(",'"+tcu.getLinkdept()+"'");
        }
        if(tcu.getOrgid() != null) {
            column.append(",ORGID");
            value.append(",'"+tcu.getOrgid()+"'");
        }
        if(tcu.getCreateuser() != null) {
            column.append(",CREATEUSER");
            value.append(",'"+tcu.getCreateuser()+"'");
        }
        if(tcu.getCreatetime() != null) {
            column.append(",CREATETIME");
            value.append(",TO_DATE('"+DateUtil.parseDate(tcu.getCreatetime(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(tcu.getMomoconcat() != null) {
            column.append(",MOMOCONCAT");
            value.append(",'"+tcu.getMomoconcat()+"'");
        }
        if(tcu.getRiskcontrol() != null) {
            column.append(",RISKCONTROL");
            value.append(",'"+tcu.getRiskcontrol()+"'");
        }
        if(tcu.getDescribe() != null) {
            column.append(",DESCRIBE");
            value.append(",'"+tcu.getDescribe()+"'");
        }
        if(tcu.getContractstaff() != null) {
            column.append(",CONTRACTSTAFF");
            value.append(",'"+tcu.getContractstaff()+"'");
        }
        if(tcu.getScrilevel() != null) {
            column.append(",SCRILEVEL");
            value.append(",'"+tcu.getScrilevel()+"'");
        }
        if(tcu.getStartdate() != null) {
            column.append(",STARTDATE");
            value.append(",TO_DATE('"+DateUtil.parseDate(tcu.getStartdate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(tcu.getEnddate() != null) {
            column.append(",ENDDATE");
            value.append(",TO_DATE('"+DateUtil.parseDate(tcu.getEnddate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(tcu.getContractitem() != null) {
            column.append(",CONTRACTITEM");
            value.append(",'"+tcu.getContractitem()+"'");
        }
        if(tcu.getContracttype() != null) {
            column.append(",CONTRACTTYPE");
            value.append(",'"+tcu.getContracttype()+"'");
        }
        if(tcu.getContractdatetype() != null) {
            column.append(",CONTRACTDATETYPE");
            value.append(",'"+tcu.getContractdatetype()+"'");
        }
        if(tcu.getContractxz() != null) {
            column.append(",CONTRACTXZ");
            value.append(",'"+tcu.getContractxz()+"'");
        }
        if(tcu.getContractxdfxinfo() != null) {
            column.append(",CONTRACTXDFXINFO");
            value.append(",'"+tcu.getContractxdfxinfo()+"'");
        }
        if(tcu.getRecordtype() != null) {
            column.append(",RECORDTYPE");
            value.append(",'"+tcu.getRecordtype()+"'");
        }
        if(tcu.getRecordparent() != null) {
            column.append(",RECORDPARENT");
            value.append(",'"+tcu.getRecordparent()+"'");
        }
        if(tcu.getHzsumowing() != null) {
            column.append(",HZSUMOWING");
            value.append(",'"+tcu.getHzsumowing()+"'");
        }
        if(tcu.getJijiatype() != null) {
            column.append(",JIJIATYPE");
            value.append(",'"+tcu.getJijiatype()+"'");
        }
        if(tcu.getMoneytype() != null) {
            column.append(",MONEYTYPE");
            value.append(",'"+tcu.getMoneytype()+"'");
        }
        if(tcu.getDctype() != null) {
            column.append(",DCTYPE");
            value.append(",'"+tcu.getDctype()+"'");
        }
        if(tcu.getContractbd() != null) {
            column.append(",CONTRACTBD");
            value.append(",'"+tcu.getContractbd()+"'");
        }
        if(tcu.getContractzd() != null) {
            column.append(",CONTRACTZD");
            value.append(",'"+tcu.getContractzd()+"'");
        }
        if(tcu.getJbstaff() != null) {
            column.append(",JBSTAFF");
            value.append(",'"+tcu.getJbstaff()+"'");
        }
        if(tcu.getJbdept() != null) {
            column.append(",JBDEPT");
            value.append(",'"+tcu.getJbdept()+"'");
        }
        if(tcu.getJbunit() != null) {
            column.append(",JBUNIT");
            value.append(",'"+tcu.getJbunit()+"'");
        }
        if(tcu.getZxunit() != null) {
            column.append(",ZXUNIT");
            value.append(",'"+tcu.getZxunit()+"'");
        }
        if(tcu.getContractchildren() != null) {
            column.append(",CONTRACTCHILDREN");
            value.append(",'"+tcu.getContractchildren()+"'");
        }
        if(tcu.getContractplan() != null) {
            column.append(",CONTRACTPLAN");
            value.append(",'"+tcu.getContractplan()+"'");
        }
        if(tcu.getTopicid() != null) {
            column.append(",TOPICID");
            value.append(",'"+tcu.getTopicid()+"'");
        }
        if(tcu.getTopicname() != null) {
            column.append(",TOPICNAME");
            value.append(",'"+tcu.getTopicname()+"'");
        }
        if(tcu.getHiscontractstatus() != null) {
            column.append(",HISCONTRACTSTATUS");
            value.append(",'"+tcu.getHiscontractstatus()+"'");
        }
        if(tcu.getChangetype() != null) {
            column.append(",CHANGETYPE");
            value.append(",'"+tcu.getChangetype()+"'");
        }
        if(tcu.getChangedate() != null) {
            column.append(",CHANGEDATE");
            value.append(",'"+tcu.getChangedate()+"'");
        }
        if(tcu.getChangedesc() != null) {
            column.append(",CHANGEDESC");
            value.append(",'"+tcu.getChangedesc()+"'");
        }
        if(tcu.getCounterpartbank() != null) {
            column.append(",COUNTERPARTBANK");
            value.append(",'"+tcu.getCounterpartbank()+"'");
        }
        column.append(")");
        value.append(")");
        String sql = column.toString()+value.toString();
        return sql;
    }

    public String updateCyhwUnit(TblCyhwUnit tcu) {
        StringBuffer sql = new StringBuffer("UPDATE TBL_CYHW_UNIT SET FLOWID = '"+tcu.getFlowid()+"'");

        if(tcu.getUnitname() != null) {
            sql.append(" ,UNITNAME = '"+tcu.getUnitname()+"'");
        }
        if(tcu.getContractname() != null) {
            sql.append(" ,CONTRACTNAME = '"+tcu.getContractname()+"'");
        }
        if(tcu.getContractno() != null) {
            sql.append(" ,CONTRACTNO = '"+tcu.getContractno()+"'");
        }
        if(tcu.getContractdept() != null) {
            sql.append(" ,CONTRACTDEPT = '"+tcu.getContractdept()+"'");
        }
        if(tcu.getContractlink() != null) {
            sql.append(" ,CONTRACTLINK = '"+tcu.getContractlink()+"'");
        }
        if(tcu.getContractmoney() != null) {
            sql.append(" ,CONTRACTMONEY = '"+tcu.getContractmoney()+"'");
        }
        if(tcu.getContractstatus() != null) {
            sql.append(" ,CONTRACTSTATUS = '"+tcu.getContractstatus()+"'");
        }
        if(tcu.getLinkdept() != null) {
            sql.append(" ,LINKDEPT = '"+tcu.getLinkdept()+"'");
        }
        if(tcu.getOrgid() != null) {
            sql.append(" ,ORGID = '"+tcu.getOrgid()+"'");
        }
        if(tcu.getCreateuser() != null) {
            sql.append(" ,CREATEUSER = '"+tcu.getCreateuser()+"'");
        }
        if(tcu.getMomoconcat() != null) {
            sql.append(" ,MOMOCONCAT = '"+tcu.getMomoconcat()+"'");
        }
        if(tcu.getRiskcontrol() != null) {
            sql.append(" ,RISKCONTROL = '"+tcu.getRiskcontrol()+"'");
        }
        if(tcu.getDescribe() != null) {
            sql.append(" ,DESCRIBE = '"+tcu.getDescribe()+"'");
        }
        if(tcu.getContractstaff() != null) {
            sql.append(" ,CONTRACTSTAFF = '"+tcu.getContractstaff()+"'");
        }
        if(tcu.getCreatetime() != null) {
            sql.append(" ,CREATETIME = TO_DATE('"+DateUtil.parseDate(tcu.getCreatetime(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(tcu.getScrilevel() != null) {
            sql.append(" ,SCRILEVEL = '"+tcu.getScrilevel()+"'");
        }
        if(tcu.getStartdate() != null) {
            sql.append(" ,STARTDATE = TO_DATE('"+DateUtil.parseDate(tcu.getStartdate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(tcu.getEnddate() != null) {
            sql.append(" ,ENDDATE = TO_DATE('"+DateUtil.parseDate(tcu.getEnddate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(tcu.getContractitem() != null) {
            sql.append(" ,CONTRACTITEM = '"+tcu.getContractitem()+"'");
        }
        if(tcu.getContracttype() != null) {
            sql.append(" ,CONTRACTTYPE = '"+tcu.getContracttype()+"'");
        }
        if(tcu.getContractdatetype() != null) {
            sql.append(" ,CONTRACTDATETYPE = '"+tcu.getContractdatetype()+"'");
        }
        if(tcu.getContractxz() != null) {
            sql.append(" ,CONTRACTXZ = '"+tcu.getContractxz()+"'");
        }
        if(tcu.getContractxdfxinfo() != null) {
            sql.append(" ,CONTRACTXDFXINFO = '"+tcu.getContractxdfxinfo()+"'");
        }
        if(tcu.getRecordtype() != null) {
            sql.append(" ,RECORDTYPE = '"+tcu.getRecordtype()+"'");
        }
        if(tcu.getHzsumowing() != null) {
            sql.append(" ,HZSUMOWING = '"+tcu.getHzsumowing()+"'");
        }
        if(tcu.getJijiatype() != null) {
            sql.append(" ,JIJIATYPE = '"+tcu.getJijiatype()+"'");
        }
        if(tcu.getMoneytype() != null) {
            sql.append(" ,MONEYTYPE = '"+tcu.getMoneytype()+"'");
        }
        if(tcu.getDctype() != null) {
            sql.append(" ,DCTYPE = '"+tcu.getDctype()+"'");
        }
        if(tcu.getContractbd() != null) {
            sql.append(" ,CONTRACTBD = '"+tcu.getContractbd()+"'");
        }
        if(tcu.getContractzd() != null) {
            sql.append(" ,CONTRACTZD = '"+tcu.getContractzd()+"'");
        }
        if(tcu.getJbstaff() != null) {
            sql.append(" ,JBSTAFF = '"+tcu.getJbstaff()+"'");
        }
        if(tcu.getJbdept() != null) {
            sql.append(" ,JBDEPT = '"+tcu.getJbdept()+"'");
        }
        if(tcu.getJbunit() != null) {
            sql.append(" ,JBUNIT = '"+tcu.getJbunit()+"'");
        }
        if(tcu.getZxunit() != null) {
            sql.append(" ,ZXUNIT = '"+tcu.getZxunit()+"'");
        }
        if(tcu.getContractchildren() != null) {
            sql.append(" ,CONTRACTCHILDREN = '"+tcu.getContractchildren()+"'");
            if("否".equals(tcu.getContractchildren()) && tcu.getRecordparent() != null && "HTGL002".equals(tcu.getRecordtype())) {
            	sql.append(" ,RECORDPARENT = NULL");
            }else if(tcu.getRecordparent() != null){
            	sql.append(" ,RECORDPARENT = '"+tcu.getRecordparent()+"'");
            }
        }
        if(tcu.getContractplan() != null) {
            sql.append(" ,CONTRACTPLAN = '"+tcu.getContractplan()+"'");
        }
        if(tcu.getTopicid() != null) {
            sql.append(" ,TOPICID = '"+tcu.getTopicid()+"'");
        }
        if(tcu.getTopicname() != null) {
            sql.append(" ,TOPICNAME = '"+tcu.getTopicname()+"'");
        }
        if(tcu.getHiscontractstatus() != null) {
            sql.append(" ,HISCONTRACTSTATUS = '"+tcu.getHiscontractstatus()+"'");
        }
        if(tcu.getChangetype() != null) {
            sql.append(" ,CHANGETYPE = '"+tcu.getChangetype()+"'");
        }
        if(tcu.getChangedate() != null) {
            sql.append(" ,CHANGEDATE = TO_DATE('"+DateUtil.parseDate(tcu.getChangedate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(tcu.getChangedesc() != null) {
            sql.append(" ,CHANGEDESC = '"+tcu.getChangedesc()+"'");
        }
        if(tcu.getCounterpartbank() != null) {
            sql.append(" ,COUNTERPARTBANK = '"+tcu.getCounterpartbank()+"'");
        }

        sql.append(" WHERE CONTRACTID = '"+tcu.getContractid()+"'");
        return sql.toString();
    }
    public String updateCyhwUnitNoDescrpib(TblCyhwUnit tcu) {
    	StringBuffer sql = new StringBuffer("UPDATE TBL_CYHW_UNIT SET FLOWID = '"+tcu.getFlowid()+"'");
    	
    	if(tcu.getUnitname() != null) {
    		sql.append(" ,UNITNAME = '"+tcu.getUnitname()+"'");
    	}
    	if(tcu.getContractname() != null) {
    		sql.append(" ,CONTRACTNAME = '"+tcu.getContractname()+"'");
    	}
    	if(tcu.getContractno() != null) {
    		sql.append(" ,CONTRACTNO = '"+tcu.getContractno()+"'");
    	}
    	if(tcu.getContractdept() != null) {
    		sql.append(" ,CONTRACTDEPT = '"+tcu.getContractdept()+"'");
    	}
    	if(tcu.getContractlink() != null) {
    		sql.append(" ,CONTRACTLINK = '"+tcu.getContractlink()+"'");
    	}
    	if(tcu.getContractmoney() != null) {
    		sql.append(" ,CONTRACTMONEY = '"+tcu.getContractmoney()+"'");
    	}
    	if(tcu.getContractstatus() != null) {
    		sql.append(" ,CONTRACTSTATUS = '"+tcu.getContractstatus()+"'");
    	}
    	if(tcu.getLinkdept() != null) {
    		sql.append(" ,LINKDEPT = '"+tcu.getLinkdept()+"'");
    	}
    	if(tcu.getOrgid() != null) {
    		sql.append(" ,ORGID = '"+tcu.getOrgid()+"'");
    	}
    	if(tcu.getCreateuser() != null) {
    		sql.append(" ,CREATEUSER = '"+tcu.getCreateuser()+"'");
    	}
    	if(tcu.getMomoconcat() != null) {
    		sql.append(" ,MOMOCONCAT = '"+tcu.getMomoconcat()+"'");
    	}
    	if(tcu.getRiskcontrol() != null) {
    		sql.append(" ,RISKCONTROL = '"+tcu.getRiskcontrol()+"'");
    	}
    	if(tcu.getContractstaff() != null) {
    		sql.append(" ,CONTRACTSTAFF = '"+tcu.getContractstaff()+"'");
    	}
    	if(tcu.getCreatetime() != null) {
    		sql.append(" ,CREATETIME = TO_DATE('"+DateUtil.parseDate(tcu.getCreatetime(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
    	}
    	if(tcu.getScrilevel() != null) {
    		sql.append(" ,SCRILEVEL = '"+tcu.getScrilevel()+"'");
    	}
    	if(tcu.getStartdate() != null) {
    		sql.append(" ,STARTDATE = TO_DATE('"+DateUtil.parseDate(tcu.getStartdate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
    	}
    	if(tcu.getEnddate() != null) {
    		sql.append(" ,ENDDATE = TO_DATE('"+DateUtil.parseDate(tcu.getEnddate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
    	}
    	if(tcu.getContractitem() != null) {
    		sql.append(" ,CONTRACTITEM = '"+tcu.getContractitem()+"'");
    	}
    	if(tcu.getContracttype() != null) {
    		sql.append(" ,CONTRACTTYPE = '"+tcu.getContracttype()+"'");
    	}
    	if(tcu.getContractdatetype() != null) {
    		sql.append(" ,CONTRACTDATETYPE = '"+tcu.getContractdatetype()+"'");
    	}
    	if(tcu.getContractxz() != null) {
    		sql.append(" ,CONTRACTXZ = '"+tcu.getContractxz()+"'");
    	}
    	if(tcu.getContractxdfxinfo() != null) {
    		sql.append(" ,CONTRACTXDFXINFO = '"+tcu.getContractxdfxinfo()+"'");
    	}
    	if(tcu.getRecordtype() != null) {
    		sql.append(" ,RECORDTYPE = '"+tcu.getRecordtype()+"'");
    	}
    	if(tcu.getRecordparent() != null) {
    		sql.append(" ,RECORDPARENT = '"+tcu.getRecordparent()+"'");
    	}
    	if(tcu.getHzsumowing() != null) {
    		sql.append(" ,HZSUMOWING = '"+tcu.getHzsumowing()+"'");
    	}
    	if(tcu.getJijiatype() != null) {
    		sql.append(" ,JIJIATYPE = '"+tcu.getJijiatype()+"'");
    	}
    	if(tcu.getMoneytype() != null) {
    		sql.append(" ,MONEYTYPE = '"+tcu.getMoneytype()+"'");
    	}
    	if(tcu.getDctype() != null) {
    		sql.append(" ,DCTYPE = '"+tcu.getDctype()+"'");
    	}
    	if(tcu.getContractbd() != null) {
    		sql.append(" ,CONTRACTBD = '"+tcu.getContractbd()+"'");
    	}
    	if(tcu.getContractzd() != null) {
    		sql.append(" ,CONTRACTZD = '"+tcu.getContractzd()+"'");
    	}
    	if(tcu.getJbstaff() != null) {
    		sql.append(" ,JBSTAFF = '"+tcu.getJbstaff()+"'");
    	}
    	if(tcu.getJbdept() != null) {
    		sql.append(" ,JBDEPT = '"+tcu.getJbdept()+"'");
    	}
    	if(tcu.getJbunit() != null) {
    		sql.append(" ,JBUNIT = '"+tcu.getJbunit()+"'");
    	}
    	if(tcu.getZxunit() != null) {
    		sql.append(" ,ZXUNIT = '"+tcu.getZxunit()+"'");
    	}
    	if(tcu.getContractchildren() != null) {
    		sql.append(" ,CONTRACTCHILDREN = '"+tcu.getContractchildren()+"'");
    	}
    	if(tcu.getContractplan() != null) {
    		sql.append(" ,CONTRACTPLAN = '"+tcu.getContractplan()+"'");
    	}
    	if(tcu.getTopicid() != null) {
    		sql.append(" ,TOPICID = '"+tcu.getTopicid()+"'");
    	}
    	if(tcu.getTopicname() != null) {
    		sql.append(" ,TOPICNAME = '"+tcu.getTopicname()+"'");
    	}
    	if(tcu.getHiscontractstatus() != null) {
    		sql.append(" ,HISCONTRACTSTATUS = '"+tcu.getHiscontractstatus()+"'");
    	}
    	if(tcu.getChangetype() != null) {
    		sql.append(" ,CHANGETYPE = '"+tcu.getChangetype()+"'");
    	}
    	if(tcu.getChangedate() != null) {
    		sql.append(" ,CHANGEDATE = TO_DATE('"+DateUtil.parseDate(tcu.getChangedate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
    	}
    	if(tcu.getChangedesc() != null) {
    		sql.append(" ,CHANGEDESC = '"+tcu.getChangedesc()+"'");
    	}
    	if(tcu.getCounterpartbank() != null) {
    		sql.append(" ,COUNTERPARTBANK = '"+tcu.getCounterpartbank()+"'");
    	}
    	
    	sql.append(" WHERE CONTRACTID = '"+tcu.getContractid()+"'");
    	return sql.toString();
    }




    public String selectListByPageInfo(PageInfo<TblCyhwUnit> pageInfo, TblCyhwUnit unit) throws Exception {
        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,ROWNUM RN FROM ( SELECT TCU.CONTRACTID,TCPAY.PAYMENMONEY,TCU.CONTRACTNAME,TCU.CONTRACTNO, TCU.CONTRACTMONEY,TCU.CONTRACTTYPE,TCU.STARTDATE,TCU.ENDDATE,TS.REALNAME,TCPB.BUDGETID,TCPB.COUNTERPARTNO,TCPB.BUDGETNAME,TCPB.COUNTERPARTHANK,TCPB.COUNTERPARTHANKACCOUNT,TCPB.PROJECTSTAGEGOAL,COUNTERPARTPHONE,(SELECT SUM(PAYMENMONEY) FROM TBL_CONTRACT_PAYMENT TCPAY WHERE TCPAY.CONTRACTID = TCU.CONTRACTID AND PAYMENTSTATUS = 6) AS PAYMONEY FROM TBL_CYHW_UNIT TCU LEFT JOIN TBL_CONTRACT_PAYMENT TCPAY ON TCPAY.CONTRACTID = TCU.CONTRACTID LEFT JOIN TBL_STAFF TS ON TS.STAFFID = TCU.CONTRACTSTAFF LEFT JOIN TBL_CYHW_PROJECTBUDGET TCPB ON TCU.CONTRACTXDFXINFO = TCPB.BUDGETID WHERE TCU.RECORDTYPE IN ('HTGL002','HTGL005') AND TCU.ORGID =  " + unit.getOrgid() + "  AND TCU.CONTRACTSTATUS = 7 AND TCU.CONTRACTID IN (SELECT PROJECTID FROM TBL_CONTRACT_PLANNODE WHERE PLANNODESTATUS = 2) AND TCU.DCTYPE = '付款' ");

        if (unit.getContractno() != null && !"".equals(unit.getContractno())) {
            sqlSb.append(" AND TCU.CONTRACTNO LIKE '%" + unit.getContractno() + "%'");
        }
        if (unit.getContractname() != null && !"".contentEquals(unit.getContractname())) {
            sqlSb.append(" AND TCU.CONTRACTNAME LIKE '%" + unit.getContractname() + "%'");
        }

        sqlSb.append(" ORDER BY TCU.CONTRACTID DESC) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;
    }

    public String selectCountByPageInfoCount(TblCyhwUnit unit) throws Exception {
        StringBuffer sqlSb = new StringBuffer("SELECT COUNT(*)  FROM TBL_CYHW_UNIT TCU LEFT JOIN TBL_STAFF TS ON TS.STAFFID = TCU.CONTRACTSTAFF LEFT JOIN TBL_CYHW_PROJECTBUDGET TCPB ON TCU.CONTRACTXDFXINFO = TCPB.BUDGETID WHERE TCU.RECORDTYPE IN ('HTGL002','HTGL005') AND TCU.ORGID = " + unit.getOrgid() + " AND TCU.CONTRACTSTATUS = 7 AND TCU.CONTRACTID IN (SELECT PROJECTID FROM TBL_CONTRACT_PLANNODE WHERE PLANNODESTATUS = 2) AND TCU.DCTYPE = '付款'");

        if (unit.getContractno() != null && !"".equals(unit.getContractno())) {
            sqlSb.append(" AND TCU.CONTRACTNO LIKE '%" + unit.getContractno() + "%'");
        }
        if (unit.getContractname() != null && !"".contentEquals(unit.getContractname())) {
            sqlSb.append(" AND TCU.CONTRACTNAME LIKE '%" + unit.getContractname() + "%'");
        }

        //sqlSb.append(" ORDER BY TCU.CONTRACTID DESC)");
        String sql = sqlSb.toString();
        return sql;
    }

    public String findListByPageInfo(PageInfo<TblCyhwUnit> pageInfo,String orgid) throws Exception {
        TblCyhwUnit unit = pageInfo.getCondition();
        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,ROWNUM RN FROM (SELECT tcu.CONTRACTID,tcu.FLOWID,tcu.UNITNAME,tcu.CONTRACTNAME,tcu.CONTRACTNO,tcu.CONTRACTDEPT,tcu.CONTRACTLINK,tcu.CONTRACTMONEY,tcu.CONTRACTSTATUS,tcu.STARTDATE,tcu.ENDDATE,tcu.CONTRACTITEM,tcu.CONTRACTTYPE,tcu.CONTRACTDATETYPE,tcu.CONTRACTXZ,tcu.RECORDTYPE,tcu.DCTYPE,tcu.CONTRACTBD from TBL_CYHW_UNIT tcu LEFT JOIN TBL_CYHW_PROJECTBUDGET tcp ON tcu.CONTRACTXDFXINFO = tcp.BUDGETID  where tcu.ORGID="+orgid);
        if(unit.getContractno()!=null && !"".equals(unit.getContractno())){
            sqlSb.append(" AND TCU.CONTRACTNO LIKE '%" + unit.getContractno() + "%'");
        }
        if(unit.getContractname() != null && !"".equals(unit.getContractname())) {
            sqlSb.append(" AND tcu.CONTRACTNAME LIKE '%"+unit.getContractname()+"%'");
        }
        if(unit.getContractbd() != null && !"".equals(unit.getContractbd())) {
            sqlSb.append(" AND tcu.CONTRACTBD LIKE '%"+unit.getContractbd()+"%'");
        }
        if(unit.getContracttype() != null && !"".equals(unit.getContracttype())) {
            sqlSb.append(" AND tcu.CONTRACTTYPE LIKE '%"+unit.getContracttype()+"%'");
        }
        if(unit.getChoicejbunitid() != null && !"".equals(unit.getChoicejbunitid())) {
            sqlSb.append(" AND tcu.LINKDEPT = '"+unit.getChoicejbunitid()+"'");
        }
        if(unit.getChoicecontractDeptId() != null && !"".equals(unit.getChoicecontractDeptId())) {
            sqlSb.append(" AND tcu.CONTRACTDEPT = '"+unit.getChoicecontractDeptId()+"'");
        }
        if(unit.getRecordConcatname() != null && !"".equals(unit.getRecordConcatname())) {
            sqlSb.append(" AND tcp.BUDGETNAME LIKE '%"+unit.getRecordConcatname()+"%'");
        }

        if(unit.getFlowid()!=null && !"".equals(unit.getFlowid())){
            sqlSb.append(" and tcu.FLOWID="+pageInfo.getCondition().getFlowid());
        }
        if(unit.getRecordtype() != null && !"".equals(unit.getRecordtype())){
            switch (unit.getRecordtype()) {
                case "HTGL002":
                    sqlSb.append("AND tcu.RECORDTYPE='HTGL002'");
                    break;
                case "HTGL005":
                    sqlSb.append(" AND tcu.RECORDTYPE = 'HTGL002' AND tcu.CONTRACTID NOT IN ( SELECT RECORDPARENT FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE='HTGL006' AND INSPECTIONSTATUS != 3)");
                    break;
                case "HTGL003":
                    sqlSb.append(" AND (tcu.RECORDTYPE = 'HTGL002' OR tcu.RECORDTYPE = 'HTGL005') AND tcu.CONTRACTID NOT IN (SELECT RECORDPARENT FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE = 'HTGL003' AND INSPECTIONSTATUS != 3) "
                            + " AND tcu.CONTRACTID NOT IN ( SELECT RECORDPARENT FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE='HTGL006' AND INSPECTIONSTATUS != 3)");
                    break;
                case "HTGL004":
                    sqlSb.append(" AND tcu.CONTRACTID IN (SELECT RECORDPARENT FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE = 'HTGL003' AND INSPECTIONSTATUS = 6) AND tcu.RECORDTYPE = 'HTGL002'");
                    break;
                case "HTGL006":
                    sqlSb.append(" AND tcu.CONTRACTID IN (SELECT RECORDPARENT FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE = 'HTGL003' AND INSPECTIONSTATUS = 6 ) AND ((SELECT COUNT(0) FROM TBL_CONTRACT_PLANNODE WHERE PROJECTID = CONTRACTID)=(SELECT COUNT(0) FROM TBL_CONTRACT_SPNODE WHERE BUDGETID IN (SELECT BUDGETID FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE = 'HTGL004' AND INSPECTIONSTATUS = 6 AND NODEFINISHDATE IS NOT NULL) "
                            +" AND NODEID IN (SELECT NODEID FROM TBL_CONTRACT_PLANNODE WHERE PROJECTID = tcu.CONTRACTID)))"
                            + " AND tcu.CONTRACTID NOT IN ( SELECT RECORDPARENT FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE='HTGL006' AND INSPECTIONSTATUS != 3)");
                    break;
                default:
                    break;
            }
        }
        if(unit.getContractstatus() != null){
            sqlSb.append(" AND tcu.CONTRACTSTATUS = "+unit.getContractstatus());
        }
        sqlSb.append(" ORDER BY tcu.createtime DESC) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;
    }

    public String findCountByPageInfoCount(PageInfo<TblCyhwUnit> pageInfo,String orgid) throws Exception {
        TblCyhwUnit unit = pageInfo.getCondition();
        StringBuffer sqlSb = new StringBuffer("SELECT COUNT(*) from TBL_CYHW_UNIT tcu LEFT JOIN TBL_CYHW_PROJECTBUDGET tcp ON tcu.CONTRACTXDFXINFO = tcp.BUDGETID  where tcu.ORGID="+orgid);
        if(unit.getContractno()!=null && !"".equals(unit.getContractno())){
            sqlSb.append(" AND TCU.CONTRACTNO LIKE '%" + unit.getContractno() + "%'");
        }
        if(unit.getContractname() != null && !"".equals(unit.getContractname())) {
            sqlSb.append(" AND tcu.CONTRACTNAME LIKE '%"+unit.getContractname()+"%'");
        }
        if(unit.getContractbd() != null && !"".equals(unit.getContractbd())) {
            sqlSb.append(" AND tcu.CONTRACTBD LIKE '%"+unit.getContractbd()+"%'");
        }
        if(unit.getContracttype() != null && !"".equals(unit.getContracttype())) {
            sqlSb.append(" AND tcu.CONTRACTTYPE LIKE '%"+unit.getContracttype()+"%'");
        }
        if(unit.getChoicejbunitid() != null && !"".equals(unit.getChoicejbunitid())) {
            sqlSb.append(" AND tcu.LINKDEPT = '"+unit.getChoicejbunitid()+"'");
        }
        if(unit.getChoicecontractDeptId() != null && !"".equals(unit.getChoicecontractDeptId())) {
            sqlSb.append(" AND tcu.CONTRACTDEPT = '"+unit.getChoicecontractDeptId()+"'");
        }
        if(unit.getRecordConcatname() != null && !"".equals(unit.getRecordConcatname())) {
            sqlSb.append(" AND tcp.BUDGETNAME LIKE '%"+unit.getRecordConcatname()+"%'");
        }

        if(unit.getFlowid()!=null && !"".equals(unit.getFlowid())){
            sqlSb.append(" and tcu.FLOWID="+pageInfo.getCondition().getFlowid());
        }
        if(unit.getRecordtype() != null && !"".equals(unit.getRecordtype())){
            switch (unit.getRecordtype()) {
                case "HTGL002":
                    sqlSb.append("AND tcu.RECORDTYPE='HTGL002'");
                    break;
                case "HTGL005":
                    sqlSb.append(" AND tcu.RECORDTYPE = 'HTGL002' AND tcu.CONTRACTID NOT IN ( SELECT RECORDPARENT FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE='HTGL006' AND INSPECTIONSTATUS != 3)");
                    break;
                case "HTGL003":
                    sqlSb.append(" AND (tcu.RECORDTYPE = 'HTGL002' OR tcu.RECORDTYPE = 'HTGL005') AND tcu.CONTRACTID NOT IN (SELECT RECORDPARENT FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE = 'HTGL003' AND INSPECTIONSTATUS != 3) "
                            + " AND tcu.CONTRACTID NOT IN ( SELECT RECORDPARENT FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE='HTGL006' AND INSPECTIONSTATUS != 3)");
                    break;
                case "HTGL004":
                    sqlSb.append(" AND tcu.CONTRACTID IN (SELECT RECORDPARENT FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE = 'HTGL003' AND INSPECTIONSTATUS = 6) AND tcu.RECORDTYPE = 'HTGL002'");
                    break;
                case "HTGL006":
                    sqlSb.append(" AND tcu.CONTRACTID IN (SELECT RECORDPARENT FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE = 'HTGL003' AND INSPECTIONSTATUS = 6 ) AND ((SELECT COUNT(0) FROM TBL_CONTRACT_PLANNODE WHERE PROJECTID = CONTRACTID)=(SELECT COUNT(0) FROM TBL_CONTRACT_SPNODE WHERE BUDGETID IN (SELECT BUDGETID FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE = 'HTGL004' AND INSPECTIONSTATUS = 6 AND NODEFINISHDATE IS NOT NULL) "
                            +" AND NODEID IN (SELECT NODEID FROM TBL_CONTRACT_PLANNODE WHERE PROJECTID = tcu.CONTRACTID)))"
                            + " AND tcu.CONTRACTID NOT IN ( SELECT RECORDPARENT FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE='HTGL006' AND INSPECTIONSTATUS != 3)");
                    break;
                default:
                    break;
            }
        }
        if(unit.getContractstatus() != null){
            sqlSb.append(" AND tcu.CONTRACTSTATUS = "+unit.getContractstatus());
        }

        return sqlSb.toString();
    }


    public String findContractSealList(PageInfo<TblCyhwUnit> pageInfo) throws Exception {
        TblCyhwUnit unit = pageInfo.getCondition();
        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,ROWNUM RN FROM "
        		+ "(SELECT tcu.CONTRACTID,SEALORG.ORGNAME SEALORGNAME,tcu.FLOWID,tcu.CONTRACTNAME,tcu.CONTRACTITEM,tcu.CONTRACTNO,TCPJ.PROJECTNAME AS TOPICNAME,tcu.CONTRACTSTATUS,tcp.COUNTERPARTCODE,tcp.COUNTERPARTHANK,NVL(tcp.INSPECTIONSTATUS,'-1') inspectionstatus,tcp.BUDGETID "
        		+ " from TBL_CYHW_UNIT tcu " +
                "  LEFT JOIN TBL_CYHW_PROJECTBUDGET tcp ON tcu.CONTRACTID = tcp.RECORDPARENT AND tcp.RECORDTYPE = '"+unit.getRecordtype()+"' " +
                "  LEFT JOIN TBL_ORGANIZATION SEALORG ON SEALORG.ORGID = tcp.SEALORGID LEFT JOIN TBL_CONTRACT_PROJECT TCPJ ON TCU.TOPICID = TCPJ.PROJECTID "+
                "  where tcu.ORGID= "+unit.getOrgid()+" AND (tcu.RECORDTYPE = 'HTGL002' OR tcu.RECORDTYPE = 'HTGL005') AND tcu.CONTRACTSTATUS >= 6");
        if(unit.getJbstaff()!= null) {
            sqlSb.append(" AND tcu.JBSTAFF = "+unit.getJbstaff());
        }
        if(unit.getContractname() != null && !"".equals(unit.getContractname())) {
            sqlSb.append( " AND tcu.CONTRACTNAME LIKE '%"+unit.getContractname()+"%'");
        }

        sqlSb.append(" ORDER BY tcu.CONTRACTID DESC) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;

    }
    public String findCountContractSealList(PageInfo<TblCyhwUnit> pageInfo) throws Exception {
        TblCyhwUnit unit = pageInfo.getCondition();
        StringBuffer sqlSb = new StringBuffer("SELECT COUNT(*) from TBL_CYHW_UNIT tcu " +
                "  LEFT JOIN TBL_CYHW_PROJECTBUDGET tcp ON tcu.CONTRACTID = tcp.RECORDPARENT AND tcp.RECORDTYPE = '"+unit.getRecordtype()+"' " +
                "  where tcu.ORGID= "+unit.getOrgid()+" AND (tcu.RECORDTYPE = 'HTGL002' OR tcu.RECORDTYPE = 'HTGL005') AND tcu.CONTRACTSTATUS >= 6");
        if(unit.getJbstaff()!= null) {
            sqlSb.append(" AND tcu.JBSTAFF = "+unit.getJbstaff());
        }
        if(unit.getContractname() != null && !"".equals(unit.getContractname())) {
            sqlSb.append( " AND tcu.CONTRACTNAME LIKE '%"+unit.getContractname()+"%'");
        }
        String sql = sqlSb.toString();
        return sql;

    }

    public String findeFilContractList(PageInfo<TblCyhwUnit> pageInfo) throws Exception {
        TblCyhwUnit unit = pageInfo.getCondition();
        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,ROWNUM RN FROM ( SELECT T1.CONTRACTID,T1.CONTRACTNAME,T1.CONTRACTNO,T1.CONTRACTMONEY,T1.CONTRACTSTATUS,T1.DCTYPE,T1.CONTRACTTYPE,T1.RECORDTYPE,T1.FLOWID  FROM ( SELECT TCU.CONTRACTID,TCU.CONTRACTNAME,TCU.CONTRACTNO,TCU.CONTRACTMONEY,TCU.CONTRACTSTATUS,TCU.DCTYPE,TCU.CONTRACTTYPE,TCU.RECORDTYPE,TCU.FLOWID FROM TBL_CYHW_UNIT TCU " +
                "                         LEFT JOIN ( " +
                "                         SELECT TCP.PROJECTID,SUM(TCI.invoiceMoney) TOTALMONEY FROM TBL_CONTRACT_PLANNODE TCP " +
                "                         LEFT JOIN TBL_CONTRACT_COLLECTION TCC ON TCP.NODEID=TCC.NODEID " +
                "                         LEFT JOIN TBL_CONTRACT_INVOICESMANAGEMEN TCI ON TCC.INVOICEID=TCI.INVOICEID " +
                "                         WHERE " +
                "                         TCP.PLANNODESTATUS=2 " +
                "                         AND TCC.collectionStatus=6 " +
                "                         AND TCI.invoiceStatus=2 " +
                "                         GROUP BY TCP.PROJECTID " +
                "                         ) SK ON TCU.CONTRACTID=SK.PROJECTID " +
                "                         WHERE TCU.ORGID="+unit.getOrgid()+" AND (TCU.RECORDTYPE = 'HTGL002' OR TCU.RECORDTYPE = 'HTGL005') AND TCU.DCTYPE='收款' AND TCU.CONTRACTSTATUS in(7,8,9,16) AND TCU.CONTRACTMONEY=SK.TOTALMONEY " +
                "                         UNION  " + //合同归档--付款
                "                         SELECT TCU.CONTRACTID,TCU.CONTRACTNAME,TCU.CONTRACTNO,TCU.CONTRACTMONEY,TCU.CONTRACTSTATUS,TCU.DCTYPE,TCU.CONTRACTTYPE,TCU.RECORDTYPE,TCU.FLOWID FROM TBL_CYHW_UNIT TCU " +
                "                         LEFT JOIN ( " +
                "                         SELECT  TCP.PROJECTID,SUM(TCPT.PAYMENMONEY) TOTALMONEY FROM TBL_CONTRACT_PLANNODE TCP " +
                "                         LEFT JOIN TBL_CONTRACT_PAYMENT TCPT ON TCP.NODEID=TCPT.NODEID " +
                "                         WHERE  " +
                "                         PLANNODESTATUS=2 " +
                "                         AND TCPT.PAYMENTSTATUS=6 " +
                "                         GROUP BY TCP.PROJECTID " +
                "                         ) SK ON TCU.CONTRACTID=SK.PROJECTID " +
                "                         WHERE TCU.ORGID="+unit.getOrgid()+" AND (TCU.RECORDTYPE = 'HTGL002' OR TCU.RECORDTYPE = 'HTGL005') AND TCU.DCTYPE='付款' AND TCU.CONTRACTSTATUS in(7,8,9,16) AND TCU.CONTRACTMONEY=SK.TOTALMONEY " +
                "                         UNION " + //合同归档 not in(付款，收款)    合同状态\n"
                "                         SELECT TCU.CONTRACTID,TCU.CONTRACTNAME,TCU.CONTRACTNO,TCU.CONTRACTMONEY,TCU.CONTRACTSTATUS,TCU.DCTYPE,TCU.CONTRACTTYPE,TCU.RECORDTYPE,TCU.FLOWID FROM TBL_CYHW_UNIT TCU " +
                "                         WHERE TCU.ORGID="+unit.getOrgid()+" AND (TCU.RECORDTYPE = 'HTGL002' OR TCU.RECORDTYPE = 'HTGL005') AND TCU.DCTYPE is NULL AND TCU.CONTRACTSTATUS in (7,8,9,16) " +
                "                         UNION "+//无履行计划\n"
                "                         SELECT TCU.CONTRACTID,TCU.CONTRACTNAME,TCU.CONTRACTNO,TCU.CONTRACTMONEY,TCU.CONTRACTSTATUS,TCU.DCTYPE,TCU.CONTRACTTYPE,TCU.RECORDTYPE,TCU.FLOWID FROM TBL_CYHW_UNIT TCU " +
                "                         LEFT JOIN ( SELECT TCP.PROJECTID FROM TBL_CONTRACT_PLANNODE TCP GROUP BY TCP.PROJECTID ) SK ON TCU.CONTRACTID = SK.PROJECTID " +
                "                         WHERE (CONTRACTPLAN ='否' OR CONTRACTPLAN  IS  NULL) AND (TCU.RECORDTYPE = 'HTGL002' OR TCU.RECORDTYPE = 'HTGL005') AND TCU.CONTRACTSTATUS in (7,8,9,16) AND SK.PROJECTID IS NULL ) T1 WHERE 1=1 ");

        if(unit.getContractno() != null && !"".equals(unit.getContractno())) {
            sqlSb.append( " AND T1.CONTRACTNO LIKE '%"+unit.getContractno()+"%'");
        }
        if(unit.getContractname() != null && !"".equals(unit.getContractname())) {
            sqlSb.append( " AND T1.CONTRACTNAME LIKE '%"+unit.getContractname()+"%'");
        }

        sqlSb.append(" ) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;


    }

    public String findeCountFilContractList(PageInfo<TblCyhwUnit> pageInfo) throws Exception {
        TblCyhwUnit unit = pageInfo.getCondition();
        StringBuffer sqlSb = new StringBuffer(" SELECT COUNT(*)  FROM ( SELECT TCU.CONTRACTID,TCU.CONTRACTNAME,TCU.CONTRACTNO,TCU.CONTRACTMONEY,TCU.CONTRACTSTATUS,TCU.DCTYPE,TCU.CONTRACTTYPE,TCU.RECORDTYPE,TCU.FLOWID FROM TBL_CYHW_UNIT TCU " +
                "                         LEFT JOIN ( " +
                "                         SELECT TCP.PROJECTID,SUM(TCI.invoiceMoney) TOTALMONEY FROM TBL_CONTRACT_PLANNODE TCP " +
                "                         LEFT JOIN TBL_CONTRACT_COLLECTION TCC ON TCP.NODEID=TCC.NODEID " +
                "                         LEFT JOIN TBL_CONTRACT_INVOICESMANAGEMEN TCI ON TCC.INVOICEID=TCI.INVOICEID " +
                "                         WHERE " +
                "                         TCP.PLANNODESTATUS=2 " +
                "                         AND TCC.collectionStatus=6 " +
                "                         AND TCI.invoiceStatus=2 " +
                "                         GROUP BY TCP.PROJECTID " +
                "                         ) SK ON TCU.CONTRACTID=SK.PROJECTID " +
                "                         WHERE TCU.ORGID="+unit.getOrgid()+" AND (TCU.RECORDTYPE = 'HTGL002' OR TCU.RECORDTYPE = 'HTGL005') AND TCU.DCTYPE='收款' AND TCU.CONTRACTSTATUS in(7,8,9,16) AND TCU.CONTRACTMONEY=SK.TOTALMONEY " +
                "                         UNION  " + //合同归档--付款
                "                         SELECT TCU.CONTRACTID,TCU.CONTRACTNAME,TCU.CONTRACTNO,TCU.CONTRACTMONEY,TCU.CONTRACTSTATUS,TCU.DCTYPE,TCU.CONTRACTTYPE,TCU.RECORDTYPE,TCU.FLOWID FROM TBL_CYHW_UNIT TCU " +
                "                         LEFT JOIN ( " +
                "                         SELECT  TCP.PROJECTID,SUM(TCPT.PAYMENMONEY) TOTALMONEY FROM TBL_CONTRACT_PLANNODE TCP " +
                "                         LEFT JOIN TBL_CONTRACT_PAYMENT TCPT ON TCP.NODEID=TCPT.NODEID " +
                "                         WHERE  " +
                "                         PLANNODESTATUS=2 " +
                "                         AND TCPT.PAYMENTSTATUS=6 " +
                "                         GROUP BY TCP.PROJECTID " +
                "                         ) SK ON TCU.CONTRACTID=SK.PROJECTID " +
                "                         WHERE TCU.ORGID="+unit.getOrgid()+" AND (TCU.RECORDTYPE = 'HTGL002' OR TCU.RECORDTYPE = 'HTGL005') AND TCU.DCTYPE='付款' AND TCU.CONTRACTSTATUS in(7,8,9,16) AND TCU.CONTRACTMONEY=SK.TOTALMONEY " +
                "                         UNION " + //合同归档 not in(付款，收款)    合同状态\n"
                "                         SELECT TCU.CONTRACTID,TCU.CONTRACTNAME,TCU.CONTRACTNO,TCU.CONTRACTMONEY,TCU.CONTRACTSTATUS,TCU.DCTYPE,TCU.CONTRACTTYPE,TCU.RECORDTYPE,TCU.FLOWID FROM TBL_CYHW_UNIT TCU " +
                "                         WHERE TCU.ORGID="+unit.getOrgid()+" AND (TCU.RECORDTYPE = 'HTGL002' OR TCU.RECORDTYPE = 'HTGL005') AND TCU.DCTYPE is NULL AND TCU.CONTRACTSTATUS in (7,8,9,16) " +
                "                         UNION "+//无履行计划\n"
                "                         SELECT TCU.CONTRACTID,TCU.CONTRACTNAME,TCU.CONTRACTNO,TCU.CONTRACTMONEY,TCU.CONTRACTSTATUS,TCU.DCTYPE,TCU.CONTRACTTYPE,TCU.RECORDTYPE,TCU.FLOWID FROM TBL_CYHW_UNIT TCU " +
                "                         LEFT JOIN ( SELECT TCP.PROJECTID FROM TBL_CONTRACT_PLANNODE TCP GROUP BY TCP.PROJECTID ) SK ON TCU.CONTRACTID = SK.PROJECTID " +
                "                         WHERE (CONTRACTPLAN ='否' OR CONTRACTPLAN  IS  NULL) AND (TCU.RECORDTYPE = 'HTGL002' OR TCU.RECORDTYPE = 'HTGL005') AND TCU.CONTRACTSTATUS in (7,8,9,16) AND SK.PROJECTID IS NULL ) T1 WHERE 1=1 ");

        if(unit.getContractno() != null && !"".equals(unit.getContractno())) {
            sqlSb.append( " AND T1.CONTRACTNO LIKE '%"+unit.getContractno()+"%'");
        }
        if(unit.getContractname() != null && !"".equals(unit.getContractname())) {
            sqlSb.append( " AND T1.CONTRACTNAME LIKE '%"+unit.getContractname()+"%'");
        }

        return sqlSb.toString();
    }

    public String findeContractListByContractStaff(PageInfo<TblCyhwUnit> pageInfo) throws Exception {
        TblCyhwUnit unit = pageInfo.getCondition();
        StringBuffer sbSql = new StringBuffer("SELECT * FROM ( SELECT BUDGET.*,ROWNUM RNUM FROM (SELECT tcu.CONTRACTID,tcu.CONTRACTNAME,tcu.CONTRACTNO,tcu.CONTRACTMONEY,tcu.CONTRACTSTATUS,tcu.DCTYPE,tcu.CONTRACTTYPE,tcu.RECORDTYPE,tcu.FLOWID FROM TBL_CYHW_UNIT tcu LEFT JOIN TBL_CYHW_PROJECTBUDGET tcp ON tcu.CONTRACTID = tcp.RECORDPARENT AND tcp.RECORDTYPE = 'HTGL003' where tcu.ORGID = " + unit.getOrgid() + " AND tcu.CONTRACTSTAFF = " + unit.getContractstaff() + " AND (tcu.RECORDTYPE = 'HTGL002' OR tcu.RECORDTYPE = 'HTGL005') AND tcu.CONTRACTSTATUS >= 6 AND tcu.CONTRACTSTATUS != 8 AND tcp.INSPECTIONSTATUS = 6 AND TCU.CONTRACTID NOT IN (SELECT RECORDPARENT FROM TBL_CYHW_UNIT where recordtype= 'HTGL005' AND CONTRACTSTATUS = 6 AND ORGID = tcu.ORGID AND RECORDPARENT IS NOT NULL) ");
        if (unit.getContractno() != null && !"".equals(unit.getContractno())) {
            sbSql.append(" AND tcu.CONTRACTNO LIKE '%" + unit.getContractno() + "%'");
        }

        if (unit.getContractname() != null && !"".equals(unit.getContractname())) {
            sbSql.append(" AND tcu.CONTRACTNAME LIKE '%" + unit.getContractname() + "%'") ;
        }

        if (unit.getContracttype() != null && !"".equals(unit.getContracttype())) {
            sbSql.append(" AND tcu.CONTRACTTYPE LIKE '%" + unit.getContracttype() + "%'") ;
        }

        if (unit.getDctype() != null && !"".equals(unit.getDctype())) {
            sbSql.append(" AND tcu.DCTYPE = '" + unit.getDctype() + "'") ;
        }
        sbSql.append("  ORDER BY tcu.CONTRACTNO DESC) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RNUM > "+pageInfo.getCurrentRecord());
        String sql = sbSql.toString();
        return sql;
    }

    public String findeCountContractListByContractStaff(PageInfo<TblCyhwUnit> pageInfo) throws Exception {
        TblCyhwUnit unit = pageInfo.getCondition();
        StringBuffer sbSql = new StringBuffer("SELECT COUNT(*) FROM TBL_CYHW_UNIT tcu LEFT JOIN TBL_CYHW_PROJECTBUDGET tcp ON tcu.CONTRACTID = tcp.RECORDPARENT AND tcp.RECORDTYPE = 'HTGL003' where tcu.ORGID = " + unit.getOrgid() + " AND tcu.CONTRACTSTAFF = " + unit.getContractstaff() + " AND (tcu.RECORDTYPE = 'HTGL002' OR tcu.RECORDTYPE = 'HTGL005') AND tcu.CONTRACTSTATUS >= 6 AND tcu.CONTRACTSTATUS != 8 AND tcp.INSPECTIONSTATUS = 6 AND TCU.CONTRACTID NOT IN (SELECT RECORDPARENT FROM TBL_CYHW_UNIT where recordtype= 'HTGL005' AND CONTRACTSTATUS = 6 AND ORGID = tcu.ORGID AND RECORDPARENT IS NOT NULL) ");
        if (unit.getContractno() != null && !"".equals(unit.getContractno())) {
            sbSql.append(" AND tcu.CONTRACTNO LIKE '%" + unit.getContractno() + "%'");
        }

        if (unit.getContractname() != null && !"".equals(unit.getContractname())) {
            sbSql.append(" AND tcu.CONTRACTNAME LIKE '%" + unit.getContractname() + "%'") ;
        }

        if (unit.getContracttype() != null && !"".equals(unit.getContracttype())) {
            sbSql.append(" AND tcu.CONTRACTTYPE LIKE '%" + unit.getContracttype() + "%'") ;
        }

        if (unit.getDctype() != null && !"".equals(unit.getDctype())) {
            sbSql.append(" AND tcu.DCTYPE = '" + unit.getDctype() + "'") ;
        }
        return sbSql.toString();
    }

    public String findeContractListPerformanceTracking(PageInfo<TblCyhwUnit> pageInfo) throws Exception {
        TblCyhwUnit unit = pageInfo.getCondition();
        StringBuffer sbSql = new StringBuffer("SELECT * FROM ( SELECT BUDGET.*,ROWNUM RNUM FROM (SELECT TCU.CONTRACTID,TCU.CONTRACTNAME,TCU.CONTRACTNO,TCU.CONTRACTMONEY,TCU.CONTRACTSTATUS,TCU.DCTYPE,TCU.CONTRACTTYPE,TCU.RECORDTYPE,TCU.FLOWID,COUNT(0) AS PLANNUM FROM TBL_CYHW_UNIT TCU LEFT JOIN TBL_CONTRACT_PLANNODE TCPN ON TCU.CONTRACTID = TCPN.PROJECTID where TCU.ORGID = " + unit.getOrgid() + " AND (TCU.RECORDTYPE = 'HTGL002' OR TCU.RECORDTYPE = 'HTGL005') AND TCU.CONTRACTSTATUS = 7 ");
        if (unit.getContractstaff() != null && !"".equals(unit.getContractstaff())) {
            sbSql.append(" AND TCU.CONTRACTSTAFF = " + unit.getContractstaff());
        }

        if (unit.getContractno() != null && !"".equals(unit.getContractno())) {
            sbSql.append( " AND TCU.CONTRACTNO LIKE '%" + unit.getContractno() + "%'");
        }

        if (unit.getContractname() != null && !"".equals(unit.getContractname())) {
            sbSql.append(" AND TCU.CONTRACTNAME LIKE '%" + unit.getContractname() + "%'");
        }

        sbSql.append("  GROUP BY TCU.CONTRACTID,TCU.CONTRACTNAME,TCU.CONTRACTNO,TCU.CONTRACTMONEY,TCU.CONTRACTSTATUS,TCU.DCTYPE,TCU.CONTRACTTYPE,TCU.RECORDTYPE,TCU.FLOWID ORDER BY TCU.CONTRACTSTATUS ASC,TCU.CONTRACTID DESC) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RNUM > "+pageInfo.getCurrentRecord());
        String sql = sbSql.toString();
        return sql;
    }

    public String findeCountContractListPerformanceTracking(PageInfo<TblCyhwUnit> pageInfo) throws Exception {
        TblCyhwUnit unit = pageInfo.getCondition();
        StringBuffer sbSql = new StringBuffer("SELECT COUNT(*) FROM (SELECT TCU.CONTRACTID,TCU.CONTRACTNAME,TCU.CONTRACTNO,TCU.CONTRACTMONEY,TCU.CONTRACTSTATUS,TCU.DCTYPE,TCU.CONTRACTTYPE,TCU.RECORDTYPE,TCU.FLOWID,COUNT(0) AS PLANNUM FROM TBL_CYHW_UNIT TCU LEFT JOIN TBL_CONTRACT_PLANNODE TCPN ON TCU.CONTRACTID = TCPN.PROJECTID where TCU.ORGID = " + unit.getOrgid() + " AND (TCU.RECORDTYPE = 'HTGL002' OR TCU.RECORDTYPE = 'HTGL005') AND TCU.CONTRACTSTATUS = 7 ");
        if (unit.getContractstaff() != null && !"".equals(unit.getContractstaff())) {
            sbSql.append(" AND TCU.CONTRACTSTAFF = " + unit.getContractstaff());
        }

        if (unit.getContractno() != null && !"".equals(unit.getContractno())) {
            sbSql.append( " AND TCU.CONTRACTNO LIKE '%" + unit.getContractno() + "%'");
        }

        if (unit.getContractname() != null && !"".equals(unit.getContractname())) {
            sbSql.append(" AND TCU.CONTRACTNAME LIKE '%" + unit.getContractname() + "%'");
        }
        sbSql.append("  GROUP BY TCU.CONTRACTID,TCU.CONTRACTNAME,TCU.CONTRACTNO,TCU.CONTRACTMONEY,TCU.CONTRACTSTATUS,TCU.DCTYPE,TCU.CONTRACTTYPE,TCU.RECORDTYPE,TCU.FLOWID )");
        return sbSql.toString();
    }

    public String findFulfillmentContract(PageInfo<TblCyhwUnit> pageInfo) {
        TblCyhwUnit unit = pageInfo.getCondition();
        StringBuffer sbSql = new StringBuffer("SELECT * FROM ( SELECT BUDGET.*,ROWNUM RNUM FROM (SELECT TCU.CONTRACTID,TCU.FLOWID,TCU.CONTRACTNAME,TCU.CONTRACTNO,TCU.CONTRACTMONEY,TCU.CONTRACTSTATUS,TCU.ORGID,TCU.RECORDTYPE,TCU.DCTYPE,TCU.CONTRACTTYPE,(SELECT COUNT(0) FROM TBL_CONTRACT_PLANNODE WHERE PROJECTID = TCU.CONTRACTID AND (PLANNODESTATUS != 2 OR PLANNODESTATUS IS NULL)) PLANNUM FROM TBL_CONTRACT_PLANNODE TCP LEFT JOIN TBL_CYHW_UNIT TCU ON TCP.PROJECTID = TCU.CONTRACTID WHERE TCP.DISPATCHSTAFF = " + unit.getJbstaff() + " AND TCU.RECORDTYPE IN ('HTGL002','HTGL005') AND TCU.CONTRACTSTATUS = 7  AND TCU.ORGID = " + unit.getOrgid());
        if (unit.getContractno() != null && !"".equals(unit.getContractno())) {
            sbSql.append( " AND TCU.CONTRACTNO LIKE '%" + unit.getContractno() + "%'");
        }

        if (unit.getContractname() != null && !"".equals(unit.getContractname())) {
            sbSql.append(" AND TCU.CONTRACTNAME LIKE '%" + unit.getContractname() + "%'");
        }
        sbSql.append("  GROUP BY TCU.CONTRACTID,TCU.FLOWID,TCU.CONTRACTNAME,TCU.CONTRACTNO,TCU.CONTRACTMONEY,TCU.CONTRACTSTATUS,TCU.ORGID,TCU.RECORDTYPE,TCU.DCTYPE,TCU.CONTRACTTYPE ORDER BY TCU.CONTRACTSTATUS ASC,TCU.CONTRACTID DESC) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RNUM > "+pageInfo.getCurrentRecord());
        String sql = sbSql.toString();
        return sql;
    }
    public String findCountFulfillmentContract(PageInfo<TblCyhwUnit> pageInfo) {
        TblCyhwUnit unit = pageInfo.getCondition();
        StringBuffer sbSql = new StringBuffer(" SELECT COUNT(0) FROM ( SELECT TCU.CONTRACTID,TCU.FLOWID,TCU.CONTRACTNAME,TCU.CONTRACTNO,TCU.CONTRACTMONEY,TCU.CONTRACTSTATUS,TCU.ORGID,TCU.RECORDTYPE,TCU.DCTYPE,TCU.CONTRACTTYPE,(SELECT COUNT(0) FROM TBL_CONTRACT_PLANNODE WHERE PROJECTID = TCU.CONTRACTID AND PLANNODESTATUS != 2) NUM FROM TBL_CONTRACT_PLANNODE TCP LEFT JOIN TBL_CYHW_UNIT TCU ON TCP.PROJECTID = TCU.CONTRACTID WHERE TCP.DISPATCHSTAFF = " + unit.getJbstaff() + " AND TCU.RECORDTYPE IN ('HTGL002','HTGL005') AND TCU.CONTRACTSTATUS = 7  AND TCU.ORGID = " + unit.getOrgid());
        if (unit.getContractno() != null && !"".equals(unit.getContractno())) {
            sbSql.append( " AND TCU.CONTRACTNO LIKE '%" + unit.getContractno() + "%'");
        }

        if (unit.getContractname() != null && !"".equals(unit.getContractname())) {
            sbSql.append(" AND TCU.CONTRACTNAME LIKE '%" + unit.getContractname() + "%'");
        }
        sbSql.append("  GROUP BY TCU.CONTRACTID,TCU.FLOWID,TCU.CONTRACTNAME,TCU.CONTRACTNO,TCU.CONTRACTMONEY,TCU.CONTRACTSTATUS,TCU.ORGID,TCU.RECORDTYPE,TCU.DCTYPE,TCU.CONTRACTTYPE )") ;
        String sql = sbSql.toString();
        return sql;
    }



    public String findLegalContractListByPageInfo(PageInfo<TblCyhwUnit> pageInfo, TblCyhwUnit tcu) {
        StringBuffer sbSql = new StringBuffer("SELECT * FROM ( SELECT BUDGET.*,ROWNUM RNUM FROM (SELECT TCU.CONTRACTID,TCU.FLOWID,TCU.CONTRACTNAME,TCU.CONTRACTNO,TCU.CONTRACTMONEY,TCU.CONTRACTTYPE,TCU.CONTRACTITEM,TCU.CONTRACTBD," +
                "TS.REALNAME FROM TBL_CYHW_UNIT TCU LEFT JOIN TBL_STAFF TS ON TCU.CONTRACTSTAFF = TS.STAFFID WHERE TCU.ORGID = " + tcu.getOrgid() + "" +
                " AND TCU.RECORDTYPE IN ('HTGL002','HTGL005') AND TCU.CONTRACTID NOT IN (SELECT RECORDPARENT FROM TBL_CYHW_UNIT WHERE ORGID = " + tcu.getOrgid() + " AND RECORDTYPE = 'HTGL005'  AND RECORDPARENT IS NOT NULL) " +
                " AND CONTRACTSTATUS NOT IN (8,12,13,14,15,16)");

        if(tcu.getContractno() != null && !"".equals(tcu.getContractno())) {
            sbSql.append(" AND CONTRACTNO LIKE '%"+tcu.getContractno()+"%'");
        }
        if(tcu.getContractname() != null && !"".equals(tcu.getContractname())) {
            sbSql.append(" AND CONTRACTNAME LIKE '%"+tcu.getContractname()+"%'");
        }

        sbSql.append(" ORDER BY TCU.CONTRACTID DESC )BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RNUM > "+pageInfo.getCurrentRecord());
        String sql = sbSql.toString();
        return sql;
    }

    public String findLegalContractListByPageInfoCount(TblCyhwUnit tcu) {
        StringBuffer sbSql = new StringBuffer("SELECT COUNT(*) FROM TBL_CYHW_UNIT TCU LEFT JOIN TBL_STAFF TS ON TCU.CONTRACTSTAFF = TS.STAFFID " +
                "WHERE TCU.ORGID = " + tcu.getOrgid() + " AND TCU.RECORDTYPE IN ('HTGL002','HTGL005') AND TCU.CONTRACTID NOT IN (SELECT RECORDPARENT FROM TBL_CYHW_UNIT WHERE ORGID = " + tcu.getOrgid() + " AND RECORDTYPE = 'HTGL005' AND RECORDPARENT IS NOT NULL) " +
                " AND CONTRACTSTATUS NOT IN (8,12,13,14,15,16)");
        if(tcu.getContractno() != null && !"".equals(tcu.getContractno())) {
            sbSql.append(" AND CONTRACTNO LIKE '%"+tcu.getContractno()+"%'");
        }
        if(tcu.getContractname() != null && !"".equals(tcu.getContractname())) {
            sbSql.append(" AND CONTRACTNAME LIKE '%"+tcu.getContractname()+"%'");
        }
        sbSql.append(" ORDER BY TCU.CONTRACTID DESC ");
        String sql = sbSql.toString();
        return sql;
    }

    public String findListByXdf(PageInfo<TblCyhwUnit> pageInfo, String budgetid) throws Exception {
        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,ROWNUM RN FROM (SELECT * from TBL_CYHW_UNIT WHERE CONTRACTXDFXINFO=" +budgetid);

        sqlSb.append(" ORDER BY CONTRACTID DESC ) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;
    }

    public String findListByXdfCount(String budgetid) throws Exception {
        StringBuffer sqlSb = new StringBuffer("SELECT COUNT(*) from TBL_CYHW_UNIT WHERE CONTRACTXDFXINFO=" +budgetid);

        //sqlSb.append(" ORDER BY CONTRACTID DESC ) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;
    }



    public String findListContractBiangengBypage(PageInfo<TblCyhwUnit> pageInfo, BigDecimal orgid, String userid, boolean findByModuel) throws Exception {
        TblCyhwUnit TCU = new TblCyhwUnit();
        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,ROWNUM RN FROM (SELECT tcu.CONTRACTID,tcu.FLOWID,tcu.UNITNAME,tcu.CONTRACTNAME,tcu.CONTRACTNO,tcu.CONTRACTDEPT,tcu.CONTRACTLINK,tcu.CONTRACTMONEY,tcu.CONTRACTSTATUS,tcu.STARTDATE,tcu.ENDDATE,tcu.CONTRACTITEM,tcu.CONTRACTTYPE,tcu.CONTRACTDATETYPE,tcu.CONTRACTXZ,tcu.RECORDTYPE,tcu.DCTYPE,tcu.CONTRACTBD from TBL_CYHW_UNIT tcu LEFT JOIN TBL_CYHW_PROJECTBUDGET tcp ON tcu.CONTRACTXDFXINFO = tcp.BUDGETID  where tcu.ORGID=" + orgid);

        if (!findByModuel) {
            sqlSb.append(" AND tcu.CREATEUSER = " + userid);
        }

        if (TCU.getContractno() != null && !"".equals(TCU.getContractno())) {
            sqlSb.append(" AND tcu.contractNo LIKE '%" + TCU.getContractno() + "%'");
        }

        if (TCU.getContractname() != null && !"".equals(TCU.getContractname())) {
            sqlSb.append( " AND tcu.CONTRACTNAME LIKE '%" + TCU.getContractname() + "%'");
        }

        if (TCU.getContractbd() != null && !"".equals(TCU.getContractbd())) {
            sqlSb.append( " AND tcu.CONTRACTBD LIKE '%" + TCU.getContractbd() + "%'");
        }

        if (TCU.getContracttype() != null && !"".equals(TCU.getContracttype())) {
            sqlSb.append( " AND tcu.CONTRACTTYPE LIKE '%" + TCU.getContracttype() + "%'");
        }

        if (TCU.getChoicejbunitid() != null && !"".equals(TCU.getChoicejbunitid())) {
            sqlSb.append( " AND tcu.LINKDEPT = '" + TCU.getChoicejbunitid() + "'");
        }

        if (TCU.getChoicecontractDeptId() != null && !"".equals(TCU.getChoicecontractDeptId())) {
            sqlSb.append(" AND tcu.CONTRACTDEPT = '" + TCU.getChoicecontractDeptId() + "'");
        }

        if (TCU.getRecordConcatname() != null && !"".equals(TCU.getRecordConcatname())) {
            sqlSb.append(" AND tcp.BUDGETNAME LIKE '%" + TCU.getRecordConcatname() + "%'");
        }

        if (TCU.getRecordtype() != null && !"".equals(TCU.getRecordtype())) {
            sqlSb.append(" AND tcu.RECORDTYPE= '" + TCU.getRecordtype() + "'");
        }

        if (TCU.getContractstatus() != null && !"".equals(TCU.getContractstatus())) {
            sqlSb.append(" AND tcu.CONTRACTSTATUS = " + TCU.getContractstatus());
        }


        sqlSb.append(" ORDER BY tcu.CONTRACTID DESC ) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;
    }


    public String findListContractBiangengBypageCount(BigDecimal orgid, String userid, boolean findByModuel) throws Exception {
        TblCyhwUnit TCU = new TblCyhwUnit();
        StringBuffer sqlSb = new StringBuffer("SELECT COUNT(*) from TBL_CYHW_UNIT tcu LEFT JOIN TBL_CYHW_PROJECTBUDGET tcp ON tcu.CONTRACTXDFXINFO = tcp.BUDGETID  where tcu.ORGID=" + orgid);

        if (!findByModuel) {
            sqlSb.append(" AND tcu.CREATEUSER = " + userid);
        }

        if (TCU.getContractno() != null && !"".equals(TCU.getContractno())) {
            sqlSb.append(" AND tcu.contractNo LIKE '%" + TCU.getContractno() + "%'");
        }

        if (TCU.getContractname() != null && !"".equals(TCU.getContractname())) {
            sqlSb.append( " AND tcu.CONTRACTNAME LIKE '%" + TCU.getContractname() + "%'");
        }

        if (TCU.getContractbd() != null && !"".equals(TCU.getContractbd())) {
            sqlSb.append( " AND tcu.CONTRACTBD LIKE '%" + TCU.getContractbd() + "%'");
        }

        if (TCU.getContracttype() != null && !"".equals(TCU.getContracttype())) {
            sqlSb.append( " AND tcu.CONTRACTTYPE LIKE '%" + TCU.getContracttype() + "%'");
        }

        if (TCU.getChoicejbunitid() != null && !"".equals(TCU.getChoicejbunitid())) {
            sqlSb.append( " AND tcu.LINKDEPT = '" + TCU.getChoicejbunitid() + "'");
        }

        if (TCU.getChoicecontractDeptId() != null && !"".equals(TCU.getChoicecontractDeptId())) {
            sqlSb.append(" AND tcu.CONTRACTDEPT = '" + TCU.getChoicecontractDeptId() + "'");
        }

        if (TCU.getRecordConcatname() != null && !"".equals(TCU.getRecordConcatname())) {
            sqlSb.append(" AND tcp.BUDGETNAME LIKE '%" + TCU.getRecordConcatname() + "%'");
        }

        if (TCU.getRecordtype() != null && !"".equals(TCU.getRecordtype())) {
            sqlSb.append(" AND tcu.RECORDTYPE= '" + TCU.getRecordtype() + "'");
        }

        if (TCU.getContractstatus() != null && !"".equals(TCU.getContractstatus())) {
            sqlSb.append(" AND tcu.CONTRACTSTATUS = " + TCU.getContractstatus());
        }


        //sqlSb.append(" ORDER BY tcu.CONTRACTID DESC ) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;
    }


    public String findListByPage(PageInfo<TblCyhwUnit> pageInfo, BigDecimal orgid) throws Exception {
        TblCyhwUnit TCU = new TblCyhwUnit();
        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,ROWNUM RN FROM (SELECT tcu.CONTRACTID,tcu.FLOWID,tcu.UNITNAME,tcu.CONTRACTNAME,tcu.CONTRACTNO,tcu.CONTRACTDEPT,tcu.CONTRACTLINK,tcu.CONTRACTMONEY,tcu.CONTRACTSTATUS,tcu.STARTDATE,tcu.ENDDATE,tcu.CONTRACTITEM,tcu.CONTRACTTYPE,tcu.CONTRACTDATETYPE,tcu.CONTRACTXZ,tcu.RECORDTYPE,tcu.DCTYPE,tcu.CONTRACTBD from TBL_CYHW_UNIT tcu LEFT JOIN TBL_CYHW_PROJECTBUDGET tcp ON tcu.CONTRACTXDFXINFO = tcp.BUDGETID  where tcu.ORGID=" + orgid);

        if (TCU.getContractno() != null && !"".equals(TCU.getContractno())) {
            sqlSb.append(" AND tcu.contractNo LIKE '%" + TCU.getContractno() + "%'");
        }

        if (TCU.getContractname() != null && !"".equals(TCU.getContractname())) {
            sqlSb.append(" AND tcu.CONTRACTNAME LIKE '%" + TCU.getContractname() + "%'");
        }

        if (TCU.getContractbd() != null && !"".equals(TCU.getContractbd())) {
            sqlSb.append(" AND tcu.CONTRACTBD LIKE '%" + TCU.getContractbd() + "%'");
        }

        if (TCU.getContracttype() != null && !"".equals(TCU.getContracttype())) {
            sqlSb.append(" AND tcu.CONTRACTTYPE LIKE '%" + TCU.getContracttype() + "%'");
        }

        if (TCU.getChoicejbunitid() != null && !"".equals(TCU.getChoicejbunitid())) {
            sqlSb.append(" AND tcu.LINKDEPT = '" + TCU.getChoicejbunitid() + "'");
        }

        if (TCU.getChoicecontractDeptId() != null && !"".equals(TCU.getChoicecontractDeptId())) {
            sqlSb.append(" AND tcu.CONTRACTDEPT = '" + TCU.getChoicecontractDeptId() + "'");
        }

        if (TCU.getRecordConcatname() != null && !"".equals(TCU.getRecordConcatname())) {
            sqlSb.append(" AND tcp.BUDGETNAME LIKE '%" + TCU.getRecordConcatname() + "%'");
        }

        if (TCU.getFlowid() != null && !"".equals(TCU.getFlowid())) {
            sqlSb.append(" and tcu.FLOWID=" + (TCU.getFlowid()));
        }

        String sqlCount;
        if (TCU.getRecordtype() != null && !"".equals(TCU.getRecordtype())) {
            sqlCount = TCU.getRecordtype();
            byte var6 = -1;
            switch(sqlCount.hashCode()) {
                case 1948486049:
                    if (sqlCount.equals("HTGL002")) {
                        var6 = 0;
                    }
                    break;
                case 1948486050:
                    if (sqlCount.equals("HTGL003")) {
                        var6 = 2;
                    }
                    break;
                case 1948486051:
                    if (sqlCount.equals("HTGL004")) {
                        var6 = 3;
                    }
                    break;
                case 1948486052:
                    if (sqlCount.equals("HTGL005")) {
                        var6 = 1;
                    }
                    break;
                case 1948486053:
                    if (sqlCount.equals("HTGL006")) {
                        var6 = 4;
                    }
            }

            switch(var6) {
                case 0:
                    sqlSb.append("AND tcu.RECORDTYPE='HTGL002'");
                    break;
                case 1:
                    sqlSb.append(" AND tcu.RECORDTYPE = 'HTGL002' AND tcu.CONTRACTID NOT IN ( SELECT RECORDPARENT FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE='HTGL006' AND INSPECTIONSTATUS != 3)");
                    break;
                case 2:
                    sqlSb.append(" AND (tcu.RECORDTYPE = 'HTGL002' OR tcu.RECORDTYPE = 'HTGL005') AND tcu.CONTRACTID NOT IN (SELECT RECORDPARENT FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE = 'HTGL003' AND INSPECTIONSTATUS != 3)  AND tcu.CONTRACTID NOT IN ( SELECT RECORDPARENT FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE='HTGL006' AND INSPECTIONSTATUS != 3)");
                    break;
                case 3:
                    sqlSb.append(" AND tcu.CONTRACTID IN (SELECT RECORDPARENT FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE = 'HTGL003' AND INSPECTIONSTATUS = 6) AND tcu.RECORDTYPE = 'HTGL002'");
                    break;
                case 4:
                    sqlSb.append(" AND tcu.CONTRACTID IN (SELECT RECORDPARENT FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE = 'HTGL003' AND INSPECTIONSTATUS = 6 ) AND ((SELECT COUNT(0) FROM TBL_CONTRACT_PLANNODE WHERE PROJECTID = CONTRACTID)=(SELECT COUNT(0) FROM TBL_CONTRACT_SPNODE WHERE BUDGETID IN (SELECT BUDGETID FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE = 'HTGL004' AND INSPECTIONSTATUS = 6 AND NODEFINISHDATE IS NOT NULL)  AND NODEID IN (SELECT NODEID FROM TBL_CONTRACT_PLANNODE WHERE PROJECTID = tcu.CONTRACTID))) AND tcu.CONTRACTID NOT IN ( SELECT RECORDPARENT FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE='HTGL006' AND INSPECTIONSTATUS != 3)");
            }
        }

        if (TCU.getContractstatus() != null) {
            sqlSb.append(" AND tcu.CONTRACTSTATUS = " + TCU.getContractstatus());
        }

        sqlSb.append(" ORDER BY tcu.createtime DESC ) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;
    }


    public String findListByPageCount(BigDecimal orgid) throws Exception {
        TblCyhwUnit TCU = new TblCyhwUnit();
        StringBuffer sqlSb = new StringBuffer("SELECT COUNT(*) TBL_CYHW_UNIT tcu LEFT JOIN TBL_CYHW_PROJECTBUDGET tcp ON tcu.CONTRACTXDFXINFO = tcp.BUDGETID  where tcu.ORGID=" + orgid);

        if (TCU.getContractno() != null && !"".equals(TCU.getContractno())) {
            sqlSb.append(" AND tcu.contractNo LIKE '%" + TCU.getContractno() + "%'");
        }

        if (TCU.getContractname() != null && !"".equals(TCU.getContractname())) {
            sqlSb.append(" AND tcu.CONTRACTNAME LIKE '%" + TCU.getContractname() + "%'");
        }

        if (TCU.getContractbd() != null && !"".equals(TCU.getContractbd())) {
            sqlSb.append(" AND tcu.CONTRACTBD LIKE '%" + TCU.getContractbd() + "%'");
        }

        if (TCU.getContracttype() != null && !"".equals(TCU.getContracttype())) {
            sqlSb.append(" AND tcu.CONTRACTTYPE LIKE '%" + TCU.getContracttype() + "%'");
        }

        if (TCU.getChoicejbunitid() != null && !"".equals(TCU.getChoicejbunitid())) {
            sqlSb.append(" AND tcu.LINKDEPT = '" + TCU.getChoicejbunitid() + "'");
        }

        if (TCU.getChoicecontractDeptId() != null && !"".equals(TCU.getChoicecontractDeptId())) {
            sqlSb.append(" AND tcu.CONTRACTDEPT = '" + TCU.getChoicecontractDeptId() + "'");
        }

        if (TCU.getRecordConcatname() != null && !"".equals(TCU.getRecordConcatname())) {
            sqlSb.append(" AND tcp.BUDGETNAME LIKE '%" + TCU.getRecordConcatname() + "%'");
        }

        if (TCU.getFlowid() != null && !"".equals(TCU.getFlowid())) {
            sqlSb.append(" and tcu.FLOWID=" + (TCU.getFlowid()));
        }

        String sqlCount;
        if (TCU.getRecordtype() != null && !"".equals(TCU.getRecordtype())) {
            sqlCount = TCU.getRecordtype();
            byte var6 = -1;
            switch(sqlCount.hashCode()) {
                case 1948486049:
                    if (sqlCount.equals("HTGL002")) {
                        var6 = 0;
                    }
                    break;
                case 1948486050:
                    if (sqlCount.equals("HTGL003")) {
                        var6 = 2;
                    }
                    break;
                case 1948486051:
                    if (sqlCount.equals("HTGL004")) {
                        var6 = 3;
                    }
                    break;
                case 1948486052:
                    if (sqlCount.equals("HTGL005")) {
                        var6 = 1;
                    }
                    break;
                case 1948486053:
                    if (sqlCount.equals("HTGL006")) {
                        var6 = 4;
                    }
            }

            switch(var6) {
                case 0:
                    sqlSb.append("AND tcu.RECORDTYPE='HTGL002'");
                    break;
                case 1:
                    sqlSb.append(" AND tcu.RECORDTYPE = 'HTGL002' AND tcu.CONTRACTID NOT IN ( SELECT RECORDPARENT FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE='HTGL006' AND INSPECTIONSTATUS != 3)");
                    break;
                case 2:
                    sqlSb.append(" AND (tcu.RECORDTYPE = 'HTGL002' OR tcu.RECORDTYPE = 'HTGL005') AND tcu.CONTRACTID NOT IN (SELECT RECORDPARENT FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE = 'HTGL003' AND INSPECTIONSTATUS != 3)  AND tcu.CONTRACTID NOT IN ( SELECT RECORDPARENT FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE='HTGL006' AND INSPECTIONSTATUS != 3)");
                    break;
                case 3:
                    sqlSb.append(" AND tcu.CONTRACTID IN (SELECT RECORDPARENT FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE = 'HTGL003' AND INSPECTIONSTATUS = 6) AND tcu.RECORDTYPE = 'HTGL002'");
                    break;
                case 4:
                    sqlSb.append(" AND tcu.CONTRACTID IN (SELECT RECORDPARENT FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE = 'HTGL003' AND INSPECTIONSTATUS = 6 ) AND ((SELECT COUNT(0) FROM TBL_CONTRACT_PLANNODE WHERE PROJECTID = CONTRACTID)=(SELECT COUNT(0) FROM TBL_CONTRACT_SPNODE WHERE BUDGETID IN (SELECT BUDGETID FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE = 'HTGL004' AND INSPECTIONSTATUS = 6 AND NODEFINISHDATE IS NOT NULL)  AND NODEID IN (SELECT NODEID FROM TBL_CONTRACT_PLANNODE WHERE PROJECTID = tcu.CONTRACTID))) AND tcu.CONTRACTID NOT IN ( SELECT RECORDPARENT FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE='HTGL006' AND INSPECTIONSTATUS != 3)");
            }
        }

        if (TCU.getContractstatus() != null) {
            sqlSb.append(" AND tcu.CONTRACTSTATUS = " + TCU.getContractstatus());
        }

      //  sqlSb.append(" ORDER BY tcu.createtime DESC ) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;
    }


    public String findListByPa(PageInfo<TblCyhwUnit> pageInfo, String userid, BigDecimal orgid) throws Exception {
        TblCyhwUnit TCU = new TblCyhwUnit();
        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,ROWNUM RN FROM (SELECT * from TBL_CYHW_UNIT where ORGID=" + orgid + "  and (CONTRACTID in (select FROMID from TBL_MY_TASK where USRID=" + userid + ") or CREATEUSER=" + userid  );

        if (TCU.getContractno() != null && !"".equals(TCU.getContractno())) {
            sqlSb.append(" AND contractNo LIKE '%" + TCU.getContractno() + "%'");
        }

        if (TCU.getFlowid() != null && !"".equals(TCU.getFlowid())) {
            sqlSb.append(" and FLOWID=" + TCU.getFlowid());
        }

        if (TCU.getContractname() != null && !"".equals(TCU.getContractname())) {
            sqlSb.append(" AND tcu.CONTRACTNAME LIKE '%" + TCU.getContractname() + "%'");
        }

        if (TCU.getContractbd() != null && !"".equals(TCU.getContractbd())) {
            sqlSb.append(" AND tcu.CONTRACTBD LIKE '%" + TCU.getContractbd() + "%'");
        }

        if (TCU.getContracttype() != null && !"".equals(TCU.getContracttype())) {
            sqlSb.append(" AND tcu.CONTRACTTYPE LIKE '%" + TCU.getContracttype() + "%'");
        }

        if (TCU.getChoicejbunitid() != null && !"".equals(TCU.getChoicejbunitid())) {
            sqlSb.append(" AND tcu.LINKDEPT = '" + TCU.getChoicejbunitid() + "'");
        }

        if (TCU.getChoicecontractDeptId() != null && !"".equals(TCU.getChoicecontractDeptId())) {
            sqlSb.append(" AND tcu.CONTRACTDEPT = '" + TCU.getChoicecontractDeptId() + "'");
        }

        if (TCU.getRecordConcatname() != null && !"".equals(TCU.getRecordConcatname())) {
            sqlSb.append(" AND tcp.BUDGETNAME LIKE '%" + TCU.getRecordConcatname() + "%'");
        }


        sqlSb.append(" ORDER BY CONTRACTID DESC ) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;
    }


    public String findListByPaCount(String userid, BigDecimal orgid) throws Exception {
        TblCyhwUnit TCU = new TblCyhwUnit();
        StringBuffer sqlSb = new StringBuffer("SELECT COUNT(*) from TBL_CYHW_UNIT where ORGID=" + orgid + "  and (CONTRACTID in (select FROMID from TBL_MY_TASK where USRID=" + userid + ") or CREATEUSER=" + userid  );

        if (TCU.getContractno() != null && !"".equals(TCU.getContractno())) {
            sqlSb.append(" AND contractNo LIKE '%" + TCU.getContractno() + "%'");
        }

        if (TCU.getFlowid() != null && !"".equals(TCU.getFlowid())) {
            sqlSb.append(" and FLOWID=" + TCU.getFlowid());
        }

        if (TCU.getContractname() != null && !"".equals(TCU.getContractname())) {
            sqlSb.append(" AND tcu.CONTRACTNAME LIKE '%" + TCU.getContractname() + "%'");
        }

        if (TCU.getContractbd() != null && !"".equals(TCU.getContractbd())) {
            sqlSb.append(" AND tcu.CONTRACTBD LIKE '%" + TCU.getContractbd() + "%'");
        }

        if (TCU.getContracttype() != null && !"".equals(TCU.getContracttype())) {
            sqlSb.append(" AND tcu.CONTRACTTYPE LIKE '%" + TCU.getContracttype() + "%'");
        }

        if (TCU.getChoicejbunitid() != null && !"".equals(TCU.getChoicejbunitid())) {
            sqlSb.append(" AND tcu.LINKDEPT = '" + TCU.getChoicejbunitid() + "'");
        }

        if (TCU.getChoicecontractDeptId() != null && !"".equals(TCU.getChoicecontractDeptId())) {
            sqlSb.append(" AND tcu.CONTRACTDEPT = '" + TCU.getChoicecontractDeptId() + "'");
        }

        if (TCU.getRecordConcatname() != null && !"".equals(TCU.getRecordConcatname())) {
            sqlSb.append(" AND tcp.BUDGETNAME LIKE '%" + TCU.getRecordConcatname() + "%'");
        }


       // sqlSb.append(" ORDER BY CONTRACTID DESC ) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;
    }
    
    
	public String getList(String orgid, String startDate) throws Exception {
		String org = "";
		String org1 = "";
		if (!StringUtils.isEmpty(orgid)) {
			org = "  AND orgid ='" + orgid + "'";
			org1 = "  AND TCU.ORGID ='" + orgid + "'";
		}
		StringBuffer sqlSb = new StringBuffer( //TCU.DCTYPE,,TCPB.BUDGETID
				" SELECT TCU.contractid,TCU.contractname,TCU.contractno,TCU.contractmoney,TCU.contractitem,TCU.fundpoolname,TCU.contracttype,TCU.moneytype,to_char(TCU.STARTDATE,'yyyy-MM-dd') as startdate,to_char(TCU.ENDDATE,'yyyy-MM-dd') as enddate,TCPB.BUDGETNAME as recordConcatname,"
						+ "CASE WHEN DCTYPE = '收款' THEN	 (SELECT SUM(INVOICEMONEY) FROM TBL_CONTRACT_INVOICESMANAGEMEN WHERE INVOICEID IN (SELECT INVOICEID FROM TBL_CONTRACT_COLLECTION WHERE CONTRACTID = TCU.CONTRACTID))"
						+ "WHEN DCTYPE = '付款' THEN  (SELECT SUM(INVOICEMONEY) FROM TBL_CONTRACT_INVOICESMANAGEMEN WHERE INVOICEID IN (SELECT INVOICEID FROM TBL_CONTRACT_PAYMENT WHERE CONTRACTID = TCU.CONTRACTID))END AS paymoney "
						+ "FROM TBL_CYHW_UNIT TCU  LEFT JOIN TBL_CYHW_PROJECTBUDGET TCPB ON TCU.CONTRACTXDFXINFO = TCPB.BUDGETID AND TCPB.RECORDTYPE = 'HTGL001'"
						+ "WHERE TCU.CONTRACTID IN (SELECT RECORDPARENT FROM TBL_CYHW_PROJECTBUDGET WHERE RECORDTYPE = 'HTGL003' AND INSPECTIONSTATUS = 6 "
						+ org + ") " + org1
						+ "AND TCU.CONTRACTID NOT IN (SELECT RECORDPARENT FROM TBL_CYHW_UNIT WHERE RECORDTYPE = 'HTGL005' AND RECORDPARENT IS NOT NULL) AND TCU.RECORDTYPE IN ('HTGL002','HTGL005')");
		if (!StringUtils.isEmpty(startDate)) {
			sqlSb.append(" and TCU.createtime >= to_date('" + startDate + "','yyyy-mm-dd') ");
		}
		String sql = sqlSb.toString();
		return sql;
	}
}

